How can I index field in this case

ubungu · 01-21-2014, 10:51 AM

Hi all

I have table 'users' with 200K rows. It have 20 fields.

Some fields in query are:
life: int(11)
last_increase: int(11)
max_life: enum('0','1')

I have query:

Code:

UPDATE `users` SET `life` = CASE
                WHEN `life` >= 0 AND `life` < 5 AND `last_increase` <= (1390320962 - 900)
                THEN (`life` + 1)
                ELSE `life`
                END,
                `last_increase` =
                CASE
                WHEN `life` >= 0 AND `life` < 6 AND `last_increase` <= (1390320962 - 900)
                THEN (1390320962)
                ELSE `last_increase`
                END
                WHERE max_life = '0';

And it take more than 10s to finish ( ~ 11 seconds)

I indexes 'last_increase' field.
Can you tell me how to index fields in this case, and why do you do it.

Thank for your helping

ubungu · 01-22-2014, 04:44 AM

Hi, please help me

ubungu · 01-24-2014, 04:11 AM

Can you help me?

chrism01 · 01-24-2014, 04:41 AM

1. Relax, this is a volunteer site; we answer when we can and if we want to ....

2. Why not use 2 simple updates instead of that

Code:

UPDATE users 
SET life = life + 1
WHERE life >= 0
AND life < 5
AND last_increase` <= (1390320962 - 900) 
AND max_life = 0
;

UPDATE users 
SET last_increase = 1390320962
WHERE life >= 0 
AND life < 6 
AND last_increase <= (1390320962 - 900)
AND max_life = 0
;

Note no need for quoting table/col names unless they are also DB / SQL keywords (not recommended anyway).
Also, I'd pre-calc (1390320962 - 900) before using it; definitely speeds things up.
You could also index life col.