Covering index and prefix indexes

Affiliate Blogs · **Covering index and prefix indexes -** 11-23-2006

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:

PLAIN TEXT
SQL:

[FONT='Courier New', Courier, monospace]CREATE TABLE `t` ([/font]
[FONT='Courier New', Courier, monospace] `i` int(11) DEFAULT NULL,[/font]
[FONT='Courier New', Courier, monospace] `j` char(10) DEFAULT NULL,[/font]
[FONT='Courier New', Courier, monospace] `k` int(11) DEFAULT NULL,[/font]
[FONT='Courier New', Courier, monospace] KEY `i` (`i`,`j`(5),`k`)[/font]
[FONT='Courier New', Courier, monospace]) ENGINE=MyISAM [/font]

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

[FONT='Courier New', Courier, monospace]mysql> EXPLAIN SELECT k FROM t WHERE i=5 \G[/font]
[FONT='Courier New', Courier, monospace]*************************** 1. row ***************************[/font]
[FONT='Courier New', Courier, monospace] id: 1[/font]
[FONT='Courier New', Courier, monospace] select_type: SIMPLE[/font]
[FONT='Courier New', Courier, monospace] TABLE: t[/font]
[FONT='Courier New', Courier, monospace] type: ref[/font]
[FONT='Courier New', Courier, monospace]possible_keys: i[/font]
[FONT='Courier New', Courier, monospace] KEY: i[/font]
[FONT='Courier New', Courier, monospace] key_len: 5[/font]
[FONT='Courier New', Courier, monospace] ref: const[/font]
[FONT='Courier New', Courier, monospace] rows: 1[/font]
[FONT='Courier New', Courier, monospace] Extra: USING WHERE; USING INDEX[/font]
[FONT='Courier New', Courier, monospace]1 row IN SET (0.00 sec) [/font]

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.
PLAIN TEXT
SQL:

[FONT='Courier New', Courier, monospace]mysql> EXPLAIN SELECT j,k FROM t WHERE i=5 \G[/font]
[FONT='Courier New', Courier, monospace]*************************** 1. row ***************************[/font]
[FONT='Courier New', Courier, monospace] id: 1[/font]
[FONT='Courier New', Courier, monospace] select_type: SIMPLE[/font]
[FONT='Courier New', Courier, monospace] TABLE: t[/font]
[FONT='Courier New', Courier, monospace] type: ref[/font]
[FONT='Courier New', Courier, monospace]possible_keys: i[/font]
[FONT='Courier New', Courier, monospace] KEY: i[/font]
[FONT='Courier New', Courier, monospace] key_len: 5[/font]
[FONT='Courier New', Courier, monospace] ref: const[/font]
[FONT='Courier New', Courier, monospace] rows: 1[/font]
[FONT='Courier New', Courier, monospace] Extra: USING WHERE[/font]
[FONT='Courier New', Courier, monospace]1 row IN SET (0.00 sec) [/font]

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.
PLAIN TEXT
SQL:

[FONT='Courier New', Courier, monospace]mysql> EXPLAIN SELECT j FROM t WHERE i=5 AND k LIKE "a%" \G[/font]
[FONT='Courier New', Courier, monospace]*************************** 1. row ***************************[/font]
[FONT='Courier New', Courier, monospace] id: 1[/font]
[FONT='Courier New', Courier, monospace] select_type: SIMPLE[/font]
[FONT='Courier New', Courier, monospace] TABLE: t[/font]
[FONT='Courier New', Courier, monospace] type: ref[/font]
[FONT='Courier New', Courier, monospace]possible_keys: i[/font]
[FONT='Courier New', Courier, monospace] KEY: i[/font]
[FONT='Courier New', Courier, monospace] key_len: 5[/font]
[FONT='Courier New', Courier, monospace] ref: const[/font]
[FONT='Courier New', Courier, monospace] rows: 1[/font]
[FONT='Courier New', Courier, monospace] Extra: USING WHERE[/font]
[FONT='Courier New', Courier, monospace]1 row IN SET (0.00 sec) [/font]

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.

Covering index and prefix indexes - Read More...