Remove "Using filesort" for simple MySQL query

Question

I am trying to remove filesort from a simple MySQL query.

The table has the column active which is either 0 or 1 and has a column season_average which is an integer, and no joins.

My select statement is as follows:

SELECT 
*
FROM `group` WHERE active=1 
ORDER BY season_average DESC

I have tried an index on active, on season_average, and multiple columns on (active, season_average) and (season_average, active) None of which solve the issue.

This SQL Fiddle shows the problem I am having: http://sqlfiddle.com/#!9/77861/2

Is there a way to get around this problem?

Answer 1

Subrata Dey Pappu had the right idea by saying that judging query performance with a small sample set is bad testing to see query performance. In your case, the query optimizer determined that it was easier to do a full table scan of 5 rows and sort the order by using a quicksort than to use the composite index (active, season_average) that you had created. This is evident by running the explain plan:
which shows the possible key choices as active_seasonAverage, however no key is used for that small of a sample set of data.

While growing your sample set size will adjust how the data is accessed by the query optimizer (as indicated by Subrata Dey Pappu in the comments above), you can trick it (this is not recommended to hardcode into your application) by running the same query but like so:

 SELECT 
*
FROM `group` force index (active_seasonAverage) WHERE active=1 
ORDER BY season_average DESC

Which forces the query optimizer to use your composite key and the explain plan will look something like this:

Notice that the key is not NULL, instead it is using active_seasonAverage and under Extra there is no Using filesort.

---

Also, with small dataset testing, consider the following excerpt from the Mysql Manual page How MySQL Uses Indexes:

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

As such, perform your testing with various indexes in place with realistic and large sets of data, running your tests thru Explain.