# Why a filesort is performed?

**URL:** <https://forums.percona.com/t/why-a-filesort-is-performed/594>\
**Category:** Other MySQL® Questions\
**Created:** [January 11, 2008, 6:52am UTC](https://forums.percona.com/t/why-a-filesort-is-performed/594 "2008-01-11T06:52:13Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sam781](https://avatars.discourse-cdn.com/v4/letter/s/22d042/32.png) [@sam781](https://forums.percona.com/u/sam781)\
**Post date:** [January 11, 2008, 6:52am UTC](https://forums.percona.com/t/why-a-filesort-is-performed/594/1 "2008-01-11T06:52:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jrmarino](https://avatars.discourse-cdn.com/v4/letter/j/ee59a6/32.png) [@jrmarino](https://forums.percona.com/u/jrmarino)\
**Post date:** [January 23, 2008, 8:29am UTC](https://forums.percona.com/t/why-a-filesort-is-performed/594/2 "2008-01-23T08:29:15Z")

</div>

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.