I'm trying to capture 'milestone' data when users pass certain metrics or scores. The score data is held on the user_credits table and changes daily. Currently just over 3M users on the table and their scores can range from 0 up to the 100's of millions. All increases only (or remain the same) never decrease.
So I'm trying to insert to a new table to capture when they pass 100, 200, 500, 1000....1M etc etc.
Currently I do this with the following statement looping around each milestone point I've defined ($mile)
insert ignore into user_milestone (select cpid,'$curdate',$mile from user_credits where metric1 > $mile and (metric1 - lastupdate) < $mile)
This certainly works but it's getting slower and slower. Explaining the statement gives the following.
mysql> explain extended select 1 from stats.user_credits where metric1 > 100 and (metric1 - lastupdate) < 100\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_credits type: range possible_keys: score key: score key_len: 8 ref: NULL rows: 3114186 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)
The 'score' index is on metric1,cpid (which is unique)
So it's having to look at all the rows on the table given the lastupdate is random like across users.
I can put in a 'high' value which helps restrict the data, say metric1 < 200 , but then it would not capture the 100 milestone if the jump was from 99 to 201.
One option would be to create a trigger for each milestone to generate the data instead. That could be a lot of triggers, not sure if it could be done in a single trigger, plus then I would have to maintain the trigger when adding new milestones.
Any other options I'm missing ??
Regards
Phil
Mar 04
Johan De Meersman Re: Capturing milestone data in a table
Mar 04, 2015; 16:00
Johan De Meersman
Re: Capturing milestone data in a table
Mar 04
Hal?sz S?ndor Re: Capturing milestone data in a table
Mar 04, 2015; 19:11
Hal?sz S?ndor
Re: Capturing milestone data in a table
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!