October 25, 2014

Covering index and prefix indexes

I already wrote in the blog as well mentioned in presentation there is often a choice you have to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only data from the index without reading the row itself.

Today I had a chance to do couple of experiments to see when exactly it works or does not work:

Now lets see if index can be used as covering index if it has some key parts which are prefixes:

Great. As you see you actually can use index which has prefix key parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column.

In this case index can’t be used as covering index as we only have portion of “j” in the index. It is interesting I tested it with single character column values which all do fit in the index but MySQL does not look at the actual data in this case it only looks at definitions.

This example is less obvious – one may think why can’t we read only from the index as we only select k column ? The reason is – we’re using column j in where clause. Even if this particular like check can be done only by using index, MySQL is not smart enough to notice it – it simply checks if column is used in the query and if it does, covering index can’t be used.

Note: MySQL is however smart enough to make sure prefix specified is actually prefix, not the full key length. If you would specify key length of 10 in this case it will convert it to the full key instead of prefix key. If you would use length longer than column length, lets say 20, MySQL will refuse to create an index.

So in general handling of prefix keys in MySQL is smart in this respect.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. L.A says:

    I have a few questions about your last query:

    First, in your explain output, you have

    key: i

    but “extra” doesn’t say “using index”. If index is not used, shouldn’t you be getting key: null?
    Or does it mean that index is being used, just not the optimal one?

    Second, how would you change your indexing so that your last query would work optimally?

    Thanks.

  2. Maarten says:

    The query of you’re last example reeds EXPLAIN SELECT j FROM t WHERE i=5 AND k LIKE “a%”, but don’t you mean EXPLAIN SELECT k FROM t WHERE i=5 AND j LIKE “a%” ?

  3. peter says:

    Oh yes. Sorry that is what I meant. Fixed now

  4. L.A says:

    I have a few questions about your last query:

    First, in your explain output, you have

    key: i

    but “extra” doesn’t say “using index”. If index is not used, shouldn’t you be getting key: null?
    Or does it mean that index is being used, just not the optimal one?

    Second, how would you change your indexing so that your last query would work optimally?

    Thanks.

Speak Your Mind

*