On Sat, Jul 19, 2008 at 6:54 AM, Jake Peavy <[EMAIL PROTECTED]> wrote:
> I may be wrong, but I think you could accomplish this through the use of
> triggers. Triggers are designed to monitor data change activity.
>
> -jp
Good thought. Using triggers to keep track of changes in an
intermediary tables allows for good performance using the approach
shown below. I think that should work well, or at least be a good
start...
mysql> DROP TABLE IF EXISTS users, balances, balances_temp, 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.11 sec)
mysql> CREATE TABLE balances_temp(
-> userid INT,
-> points INT,
-> points_original INT,
-> PRIMARY KEY(userid) );
Query OK, 0 rows affected (0.16 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.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> CREATE TRIGGER users_insert
-> AFTER INSERT ON users
-> FOR EACH ROW
-> INSERT INTO balances_temp(userid,points) VALUES(NEW.userid,NEW.points);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER users_update
-> BEFORE UPDATE ON users
-> FOR EACH ROW
-> INSERT INTO balances_temp(userid,points,points_original) VALUES(NEW.useri
d,NEW.points,OLD.points)
-> ON DUPLICATE KEY UPDATE points = VALUES(points);
Query OK, 0 rows affected (0.01 sec)
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.69 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> INSERT INTO balances(userid, points)
-> SELECT userid, points
-> FROM balances_temp
-> WHERE points != points_original OR points_original IS NULL ;
Query OK, 10000 rows affected (0.30 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> DELETE FROM balances_temp;
Query OK, 10000 rows affected (0.28 sec)
mysql> SELECT SLEEP(60);
+-----------+
| SLEEP(60) |
+-----------+
| 0 |
+-----------+
1 row in set (1 min 0.00 sec)
mysql> UPDATE users
-> SET points = rand()*100
-> WHERE rand()< .3;
Query OK, 3014 rows affected (0.53 sec)
Rows matched: 3044 Changed: 3014 Warnings: 0
mysql> INSERT INTO balances(userid, points)
-> SELECT userid, points
-> FROM balances_temp
-> WHERE points != points_original OR points_original IS NULL ;
Query OK, 3014 rows affected (0.19 sec)
Records: 3014 Duplicates: 0 Warnings: 0
mysql> DELETE FROM balances_temp;
Query OK, 3044 rows affected (0.13 sec)
mysql> SELECT * FROM balances WHERE userid < 10 ORDER BY timestamp_dump, userid;
+--------+--------+---------------------+
| userid | points | timestamp_dump |
+--------+--------+---------------------+
| 1 | 42 | 2008-07-19 13:57:54 |
| 2 | 76 | 2008-07-19 13:57:54 |
| 3 | 50 | 2008-07-19 13:57:54 |
| 4 | 24 | 2008-07-19 13:57:54 |
| 5 | 70 | 2008-07-19 13:57:54 |
| 6 | 76 | 2008-07-19 13:57:54 |
| 7 | 72 | 2008-07-19 13:57:54 |
| 8 | 33 | 2008-07-19 13:57:54 |
| 9 | 48 | 2008-07-19 13:57:54 |
| 1 | 7 | 2008-07-19 13:58:56 |
| 2 | 33 | 2008-07-19 13:58:56 |
| 8 | 44 | 2008-07-19 13:58:56 |
| 9 | 19 | 2008-07-19 13:58:56 |
+--------+--------+---------------------+
13 rows in set (0.22 sec)
--
Rob Wultsch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
