On Sat, Jul 19, 2008 at 4:18 AM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> On Fri, Jul 18, 2008 at 3:46 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
>> I'm sure there is a way to write this in a single query, but I bet it
>> it is ugly as heck, probably with a bunch of derived tables.
>
> INSERT INTO balances (userid, points)
> SELECT users.userid, users.points
> FROM users
> LEFT JOIN (
> SELECT userid, MAX(timestamp_dump) AS 'timestamp_dump'
> FROM balances
> GROUP BY userid
> ) AS b1 USING(userid)
> LEFT JOIN balances b2 USING(userid,timestamp_dump)
> WHERE users.points != b2.points
> OR b2.points IS NULL ;
>
> This query is a dog, That stored procedure is pretty horrible. I'm
> going to play with trying to figure out how to optimize this. Fun
> stuff.
Better version of the query broken up a bit above:
DROP TABLE IF EXISTS balances_temp;
CREATE TEMPORARY TABLE balances_temp(userid INT, timestamp_dump
timestamp,INDEX(userid) )
SELECT userid, MAX(timestamp_dump) AS 'timestamp_dump'
FROM balances
GROUP BY userid;
INSERT INTO balances (userid, points)
SELECT users.userid, users.points
FROM users
LEFT JOIN balances_temp AS b1 USING(userid)
LEFT JOIN balances b2 USING(userid,timestamp_dump)
WHERE users.points != b2.points OR b2.points IS NULL;
This runs ~30 seconds for 500k users, and will of course work with all
version of mysql. I did not realize that derived table performance was
that bad...
--
Rob Wultsch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
