Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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())<=30totimestampadd(MINUTE, -30, now()) >= tmUpdateand an index onsat_msgPos (idVeh, idTrigger, tmUpdate)orsat_msgPos (idTrigger, idVeh, tmUpdate), ifidTriggeris more selective thanidVeh. – sticky bit Jun 11 '18 at 11:15EXPLAIN SELECT ...;) – ypercubeᵀᴹ Jun 23 '18 at 11:28