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?

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, 2018 at 11:15
  • Same thinks. I have made all the changes, but it takes over 1 minute to complete the query. Any other ideea? Jun 12, 2018 at 11:16
  • @stickybit that suggestion should be answer. Will you add it? Jun 23, 2018 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 ...;) Jun 23, 2018 at 11:28

1 Answer 1

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).

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.