Why a filesort is performed?

MySQL & MariaDB Other MySQL® Questions
1

Hi @all,

first of all the query:

SELECT customer_id
FROM Campaign2customer
WHERE campaign_id =4
AND last_time BETWEEN “08:00:00” AND “10:00:00”
ORDER BY last_datetime
LIMIT 1

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

Greets
Sam781

2

Yeah, Sam, I have run into that before.

Let’s assume that you kept the recent change to the index and it is defined as (campaign_id, last_datetime, last_time).

What would have worked is changing the ORDER BY clause to “ORDER BY campaign_id, last_datetime”

If the clause matches your index, then it won’t filesort. It’s not obvious to sort by campaign_id because all the returned rows will have the same one, but mysql isn’t smart enough to realize that it can use an existing index.

if you don’t want to change the index again, you’ll have to change the clause to “ORDER BY campaign_id, last_time, last_datetime” to match the key.