MySQL query takes too long

Question

I have made this query:

SELECT COUNT(*) 
from sat_msgPos 
where  TIMESTAMPDIFF(MINUTE,tmUpdate,NOW())<=30 
  and idTrigger = 9 
  and idVeh = 12590; 

and an index (idVeh,tmUpdate) on a big table. It's much better after I created the index, but I want to make it faster.

Does anybody have a suggestion?

Answer 1

The WHERE codition has a function that acts on a column. This means that even if an index is used, it will have to do a complete index scan. In such cases, it is good (if possible) to rewrite the condition so the column is used bare, and any function is applied to constants or run-time constants (like NOW()). This way an index can be used with an index seek - and a partial index scan, only rows that are needed will be scanned.

Try rewriting

TIMESTAMPDIFF(MINUTE, tmUpdate, NOW()) <= 30 

to:

timestampadd(MINUTE, -30, now()) >= tmUpdate 

and add an index on either sat_msgPos (idVeh, idTrigger, tmUpdate) or sat_msgPos (idTrigger, idVeh, tmUpdate).