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:
1 2 3 4 5 6 | CREATE TABLE `t` ( `i` int(11) default NULL, `j` char(10) default NULL, `k` int(11) default NULL, KEY `i` (`i`,`j`(5),`k`) ) ENGINE=MyISAM |
Now lets see if index can be used as covering index if it has some key parts which are prefixes:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> explain select k from t where i=5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: i key: i key_len: 5 ref: const rows: 1 Extra: Using where; Using index 1 row in set (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> explain select j,k from t where i=5 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: i key: i key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> explain select k from t where i=5 and j like "a%" \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: i key: i key_len: 5 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) |
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.
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%” ?
Oh yes. Sorry that is what I meant. Fixed now
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.
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.