Search code examples
mysqlindexingprefixcomposite-key

How to use a prefix index for an index-only scan in MySQL

Imagine I have a (huge) table like so:

category   type  
--------   ----
foo        EC22
foo        EC00
bar        EC00
bar        EDC0
...        ...

The first two characters in type have a special meaning and I'm only interested in those for SELECT purposes. I want to use a composite index with a prefix on type like so: category, type(2)

Now when I do:

EXPLAIN SELECT category, type FROM table
WHERE category = 'foo'
AND LEFT(type,2) = 'EC'

... it tells me MySQL is Using index condition; (meaning rows are read to doublecheck the index).

I want to use everything that has an index value of EC and continue with the rest of my index-only scan. E.g. EXPLAIN telling me Using index; (without condition). There is no need to double check the actual value of this field, because I'm only looking at the first two characters. Is it possible to achieve or force this?

Update

I can SET optimizer_switch='index_condition_pushdown=off'; and then EXPLAIN changes from Using index lookup; to Using where; and it's about 15% faster. I guess I'm not entirely sure what's going on here and how I can see my query is done using indices alone.

Solution

  • When EXPLAIN shows "Using index", that means that the index is a covering index for the query. That is, the query can be satisfied entirely from the index blocks, without requiring any lookups of rows in the underlying table blocks.

    Look at your query again. And notice that it's returning the type column (the expression in the SELECT list.) That's the whole column. And that whole column is not available in the index.

    So that index can't be a covering index for the query, so MySQL isn't ever going to show 'Using index' in the EXPLAIN output (with that query and that index.)

    Since it's not a covering index for the query, MySQL will have to do the lookup to the underlying data page to get the value of the column so it can be returned.

    Now as far as whether the index is being used to check the condition LEFT(type,2) = 'EC', we'd need to check the key_len in the EXPLAIN output.

    We can compare the key_len in the EXPLAIN when there is no condition on the type column vs when there is a condition. I'd also test with a condition such as type LIKE 'EC%'.

    I'd compare key_len from the EXPLAIN for all of these:

     SELECT category, type FROM huge_table WHERE category = 'bar' ; 
 SELECT category, type FROM huge_table WHERE category = 'bar' AND type LIKE 'E%' ; 
 SELECT category, type FROM huge_table WHERE category = 'bar' AND type LIKE 'EC%' ;
 SELECT category, type FROM huge_table WHERE category = 'bar' AND LEFT(type,1) = 'E' ;
 SELECT category, type FROM huge_table WHERE category = 'bar' AND LEFT(type,2) = 'EC' ;

    If key_len is the same (i.e. the length of just the category column) in all those cases, then that suggests that MySQL isn't making use the index to check the LEFT(type,2) = condition.

    And you would be correct. MySQL is visiting the underlying data page before it checks the condition.

    But if key_len is longer in some of those cases, that suggests that MySQL is checking the condition from the index, before it does the lookup of the row.

    You might also get an EXPLAIN for a query that doesn't include the type column in the SELECT list.