MySQL indexing and Using filesort

In your first query, the ORDER BY is done using the views_point INDEX, because it was used in the WHERE part of the query and therefore in MySQL can be used for sorting.

In the second query, MySQL resolves the WHERE part using a different index, listing_pcs. This cannot be used to satisfy the ORDER BY condition. MySQL uses filesort instead, which is the best option if an index cannot be used.

MySQL only uses indexes to sort if the index is the same as that used in the WHERE condition. This is what the manual means by:

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

The key used to fetch the rows is not the same as the one used in the ORDER BY:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

So what can you do:

1. Try increasing your sort_buffer_size config option to make filesorting as effective as possible. Large results that are too big for the sort buffer cause MySQL to break the sort down into chunks, which is slower.

2. Force MySQL to choose a different index. It’s worth noting that different MySQL versions choose default indexes differently. Version 5.1, for example, is pretty bad as the Query Optimizer had been vastly re-written for this release and needed lots of refinement. Version 5.6 is pretty good.

   SELECT *
   FROM listings
   FORCE INDEX (views_point)
   WHERE (`publishedon_hourly` BETWEEN
          UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
          AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' ))
     AND (published =1)
     AND cat_id IN ( 1, 2, 3, 4, 5 )
   ORDER BY `views_point` DESC
   LIMIT 10