On 7/19/08, Rob Wultsch <[EMAIL PROTECTED]> wrote: > > 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... >
I guess this is where we make assumptions about the OPs schema and details of his methodology. My assumption is that this is simpler than your solution. The users table currently exists and is seeded with 500,000 users. I would use a straight select to baseline the user_changelog, then install the trigger on users and let it do all the work from then on. The only question is what happens when a new user is created - a second INSERT trigger might be required. Of course you could also store the pre-UPDATE point balance. mysql> DROP TABLE IF EXISTS users,user_changelog; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> DROP TRIGGER IF EXISTS user_trigger; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE users (userid INT PRIMARY KEY AUTO_INCREMENT,point_balance INT DEFAULT 0); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO users VALUES (1,10*RAND()),(2,10*RAND()),(3,10*RAND()),(4,10*RAND()),(5,10*RAND()); Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE user_changelog (userid INT,point_balance INT,update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX id (userid)); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO user_changelog (userid,point_balance) SELECT * FROM users; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> CREATE TRIGGER user_trigger AFTER UPDATE ON users FOR EACH ROW INSERT INTO user_changelog(userid,point_balance) VALUES (NEW.userid,NEW.point_balance); Query OK, 0 rows affected (0.01 sec) mysql> SELECT SLEEP(15); +-----------+ | SLEEP(15) | +-----------+ | 0 | +-----------+ 1 row in set (15.03 sec) mysql> UPDATE users SET point_balance=11 WHERE userid=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM users; +--------+---------------+ | userid | point_balance | +--------+---------------+ | 1 | 11 | | 2 | 7 | | 3 | 6 | | 4 | 8 | | 5 | 0 | +--------+---------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM user_changelog; +--------+---------------+---------------------+ | userid | point_balance | update_time | +--------+---------------+---------------------+ | 1 | 7 | 2008-07-20 09:30:57 | | 2 | 7 | 2008-07-20 09:30:57 | | 3 | 6 | 2008-07-20 09:30:57 | | 4 | 8 | 2008-07-20 09:30:57 | | 5 | 0 | 2008-07-20 09:30:57 | | 1 | 11 | 2008-07-20 09:31:12 | +--------+---------------+---------------------+ 6 rows in set (0.00 sec) Anyway, this should be enough to get the OP going. -jp -- I hope that someday we will be able to put away our fears and prejudices and just laugh at people. deepthoughtsbyjackhandy.com
