Hi mysql experts,
I feel like I'm missing something.
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
