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.
>
>
> --
> Rob Wultsch
>
Unless I am missing something Postgres is 100X faster for the query shown above.
Awesome...
postgres=# DROP TABLE IF EXISTS users, balances;
DROP TABLE
postgres=# CREATE TABLE users(
postgres(# userid SERIAL,
postgres(# points INT);
NOTICE: CREATE TABLE will create implicit sequence "users_userid_seq" for seria
l column "users.userid"
CREATE TABLE
postgres=# CREATE TABLE balances(
postgres(# userid INT,
postgres(# points INT,
postgres(# timestamp_dump TIMESTAMP DEFAULT NOW(),
postgres(# PRIMARY KEY(userid,timestamp_dump) );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "balances_pkey" f
or table "balances"
CREATE TABLE
postgres=# INSERT INTO users(points)
postgres-# SELECT random()*100 FROM generate_series(0,10000) ;
INSERT 0 10001
postgres=# INSERT INTO balances (userid, points)
postgres-# SELECT users.userid, users.points
postgres-# FROM users
postgres-# LEFT JOIN (
postgres(# SELECT userid, MAX(timestamp_dump) AS timestamp_dump
postgres(# FROM balances
postgres(# GROUP BY userid
postgres(# ) AS b1 USING(userid)
postgres-# LEFT JOIN balances b2 USING(userid,timestamp_dump)
postgres-# WHERE users.points != b2.points
postgres-# OR b2.points IS NULL ;
INSERT 0 10001
postgres=# UPDATE users
postgres-# SET points = random()*100
postgres-# WHERE random()< .3;
UPDATE 2976
postgres=# select now();
now
----------------------------
2008-07-19 06:26:31.359-07
(1 row)
postgres=# INSERT INTO balances (userid, points)
postgres-# SELECT users.userid, users.points
postgres-# FROM users
postgres-# LEFT JOIN (
postgres(# SELECT userid, MAX(timestamp_dump) AS timestamp_dump
postgres(# FROM balances
postgres(# GROUP BY userid
postgres(# ) AS b1 USING(userid)
postgres-# LEFT JOIN balances b2 USING(userid,timestamp_dump)
postgres-# WHERE users.points != b2.points
postgres-# OR b2.points IS NULL ;
INSERT 0 2946
postgres=# select now();
now
----------------------------
2008-07-19 06:26:31.593-07
(1 row)
mysql> DROP TABLE IF EXISTS users, balances, integers;
Query OK, 0 rows affected (0.41 sec)
mysql> CREATE TABLE users(
-> userid INT PRIMARY KEY AUTO_INCREMENT,
-> points INT);
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE balances(
-> userid INT,
-> points INT,
-> timestamp_dump TIMESTAMP,
-> PRIMARY KEY(userid,timestamp_dump) );
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE integers(i tinyint);
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO integers(i)
-> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 10 rows affected (0.06 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO users(points)
-> SELECT rand()*100
-> FROM integers AS i1,
-> integers AS i2,
-> integers AS i3,
-> integers AS i4
-> WHERE i1.i + i2.i *10 + i3.i * 100 + i4.i *1000 < 10000 ;
Query OK, 10000 rows affected (0.31 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> 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 ;
Query OK, 10000 rows affected (0.38 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> UPDATE users
-> SET points = rand()*100
-> WHERE rand()< .3;
Query OK, 2970 rows affected (0.16 sec)
Rows matched: 2990 Changed: 2970 Warnings: 0
mysql> 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 ;
Query OK, 2970 rows affected (20.05 sec)
Records: 2970 Duplicates: 0 Warnings: 0
--
Rob Wultsch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
