0

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?

Improve this question
4
  • 3
    Try rewriting TIMESTAMPDIFF(MINUTE,tmUpdate,NOW())<=30 to timestampadd(MINUTE, -30, now()) >= tmUpdate and an index on sat_msgPos (idVeh, idTrigger, tmUpdate) or sat_msgPos (idTrigger, idVeh, tmUpdate), if idTrigger is more selective than idVeh.
    – sticky bit
    Jun 11 '18 at 11:15
  • Same thinks. I have made all the changes, but it takes over 1 minute to complete the query. Any other ideea?
    – Isabella Popa
    Jun 12 '18 at 11:16
  • @stickybit that suggestion should be answer. Will you add it?
    – ypercubeᵀᴹ
    Jun 23 '18 at 11:23
  • @IsabellaPopa did you do everything that stickybit suggested? (see my answer as well). If yes, please edit the question and add the new execution plan (output of EXPLAIN SELECT ...;)
    – ypercubeᵀᴹ
    Jun 23 '18 at 11:28
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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.