On Fri, Jul 18, 2008 at 2:00 PM, Jason Yergeau <[EMAIL PROTECTED]> wrote:
> I'm having trouble working through a data problem. Any tips or
> keywords that might clue me into a known pattern would be incredibly
> appreciated!
>
> I have about 500,000 users in my system. Each user has a points
> balance, updated by a system over which I have no control. I'd like
> to track changes to each user's point balance over time by taking
> timestamped snapshots of their balance, and saving it into a new
> table.
>
> It's easy to take the snapshot:
>
> insert into balances (userid, points) select userid, points from users;
>
> This quickly takes the points field from my users table, and saves it
> into a balances table, which saves the data along with a timestamp. I
> can run that query on a regular basis without overly taxing my system.
>
> The first time its run, I get 500,000 rows of data. That's fine. But
> the next time I run a query, I only want to save the differences in
> balance.
>
> Anyone have any tips?
>
> Best,
> Jason
>
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. Here is a
solution using a stored proc:
mysql> DROP TABLE users, balances;
Query OK, 0 rows affected (0.19 sec)
mysql> CREATE TABLE users(
-> userid int,
-> points int);
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE balances(
-> userid int,
-> points int,
-> timestamp_dump timestamp);
Query OK, 0 rows affected (0.20 sec)
mysql> DROP PROCEDURE IF EXISTS pull_dump;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER //
mysql> CREATE PROCEDURE pull_dump ()
-> BEGIN
-> DECLARE done,alt_done INT DEFAULT 0;
-> DECLARE users_userid,users_points,balances_points INT;
-> DECLARE cur1 CURSOR FOR SELECT userid,points FROM users;
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
->
-> OPEN cur1;
-> REPEAT
-> FETCH cur1 INTO users_userid,users_points;
-> IF NOT done THEN
-> SET alt_done = done;
-> SELECT points INTO balances_points
-> FROM balances
-> WHERE userid =users_userid
-> ORDER BY timestamp_dump DESC
-> LIMIT 1;
-> SET done = alt_done;
->
-> IF users_points != balances_points OR balances_points IS
NULL THEN
-> INSERT INTO balances (userid,points) VALUES (use
rs_userid,users_points);
-> END IF;
-> END IF;
-> UNTIL done END REPEAT;
-> CLOSE cur1;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> INSERT INTO users(userid,points) VALUES(1,5),(2,10),(3,15);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CALL pull_dump();
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM balances;
+--------+--------+---------------------+
| userid | points | timestamp_dump |
+--------+--------+---------------------+
| 1 | 5 | 2008-07-18 15:39:38 |
| 2 | 10 | 2008-07-18 15:39:38 |
| 3 | 15 | 2008-07-18 15:39:38 |
+--------+--------+---------------------+
3 rows in set (0.00 sec)
mysql> UPDATE users
-> SET points =20
-> WHERE userid =3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> CALL pull_dump();
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM balances;
+--------+--------+---------------------+
| userid | points | timestamp_dump |
+--------+--------+---------------------+
| 1 | 5 | 2008-07-18 15:39:38 |
| 2 | 10 | 2008-07-18 15:39:38 |
| 3 | 15 | 2008-07-18 15:39:38 |
| 3 | 20 | 2008-07-18 15:39:47 |
+--------+--------+---------------------+
4 rows in set (0.00 sec)
Test this on dev before using on production. I have no idea how fast
this will be...
--
Rob Wultsch
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
