Search code examples
mysqlindexingprefix

How does MySQL use ORDER BY when referencing an indexed prefix on VARCHAR?

I'm curious how this is handled. I have a varchar(255) column named title that has the first 10 characters indexed.

If I create a MySQL query that orders by title, does it take advantage of that index solely, or does it ignore it, or do I have to issue a command to use that index?

For example let's say I have two title items named:

This is the same thing
This is the same thing only slightly different

The first 10 chars are the same, so how does MySQL handle that? Does it use the prefix index up to the first 10 chars and then row by row index afterwards?

Solution

  • Well, it's said that:

    The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause.

    But I strongly suggest checking this query with EXPLAIN, to see which path the optimizer will choose.