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)
.
TIMESTAMPDIFF(MINUTE,tmUpdate,NOW())<=30
totimestampadd(MINUTE, -30, now()) >= tmUpdate
and an index onsat_msgPos (idVeh, idTrigger, tmUpdate)
orsat_msgPos (idTrigger, idVeh, tmUpdate)
, ifidTrigger
is more selective thanidVeh
.EXPLAIN SELECT ...;
)