MySQL Forums
Forum List  »  Performance

Why a filesort is performed?
Posted by: Samir Najib
Date: January 24, 2008 04:14AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Why a filesort is performed?
2176
January 24, 2008 04:14AM
1239
January 24, 2008 05:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.