first of all the query:
WHERE campaign_id =4
AND last_time BETWEEN “08:00:00” AND “10:00:00”
ORDER BY last_datetime
The index is (campaign_id, last_time, last_datetime).
In this case the explain says that a filesort is performed.
I have tried to change the index to (campaign_id, last_datetime, last_time). The filesort was gone, but much more rows are selected, because the row restriction by ‘…last_time BETWEEN…’ is not done with the index anymore.
Can anybody please explain me why the heck a filesort is done in the first case? The columns in the index are exactly in the same order as used in the query…
Perhaps someone does have a solution to index the row restriction and the sorting for this query!?
Thank you very much in advance! )