MySQL InnoDB Sorting Issue
I have some rather distressing news: ORDER BY can still wreak some havoc with filesorts.

With all the hype about this being addressed and fixed, there is simply no way to get InnoDB to effectively use the index on an ORDER BY.

Start with the Ground Zero of InnoDB row data, the Clustered Index.

Rows are tagged with


a 6-byte transaction ID field
a 7-byte roll pointer field


Rows tend to be ordered by the whatever order the data was entered. The columns of a PRIMARY KEY are included in secondary indexes and are used to search for rows in the Clustered Index, Unfortunately, the two ID fields are not really used in dictating any ordering of rows within the Clustered Index. (For more info, please see MySQL Documentation on InnoDB Physical Row Structure)

Here is something even more disturbing: Did you know you could order rows in a table by the columns of the PRIMARY KEY or any arbitrary ordering you choose?

Here is the syntax:

ALTER TABLE tblname ORDER BY col_name [, col_name] ...


This could speed up some queries that a PRIMARY KEY ordered, but what's disturbing is that it only applies to MyISAM tables. Why not InnoDB ?

According to the MySQL Documentation on ALTER TABLE ... ORDER BY:


  ORDER BY enables you to create the new table with the rows in a
  specific order. Note that the table does not remain in this order
  after inserts and deletes. This option is useful primarily when you
  know that you are mostly to query the rows in a certain order most of
  the time. By using this option after major changes to the table, you
  might be able to get higher performance. In some cases, it might make
  sorting easier for MySQL if the table is in order by the column that
  you want to order it by later.
  
  ORDER BY syntax permits one or more column names to be specified for
  sorting, each of which optionally can be followed by ASC or DESC to
  indicate ascending or descending sort order, respectively. The default
  is ascending order. Only column names are permitted as sort criteria;
  arbitrary expressions are not permitted. This clause should be given
  last after any other clauses.
  
  ORDER BY does not make sense for InnoDB tables because InnoDB always
  orders table rows according to the clustered index.


This comes as no surprise to me since I mentioned this in one of my earlier posts : (Aug 29, 2011 : Preordering the table by a specified column)

Therefore, doing an ORDER BY on an InnoDB table never guarantees proper index selection due to its internal index organization. Thus, one should not be surprised by a filesort on an InnoDB table no matter what secondary indexes the table has.