MySQL Composite Index – Usage with WHERE by 1st and 3rd Column

indexMySQL

Let's consider a table with 3 varchar columns and a composite index on all of them in the order of (a,b,c):

CREATE TABLE `test` (
      `a` VARCHAR(255) NOT NULL,
      `b` VARCHAR(255) NOT NULL,
      `c` VARCHAR(255) NOT NULL,
      INDEX(`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

Will the composite index be used in case of WHERE a = const AND c = const condition? Here it is stated it couldn't be used, however, if we launch EXPLAIN for such a table/query on a DB Fiddle we see a in possible_keys and keys, so looks like it is going to be used at least for the first part of the condition.

UPDATE: I think I used wrong wording in my initial question. I would like to know if the composite index could be used in case of WHERE a = const AND c = const and to object to the following clause in the link above:

Notice that if you have a composite index on (c1,c2,c3), you will have
indexed search capabilities on one the following column combinations:

(c1) (c1,c2) (c1,c2,c3)

I do realize that it could not be used in case of (c2,c3), but I'm asking about (c1,c3)

Best Answer

There are few factors that affects MySQL decision whether to use an index or no.

This composite index can be used in some cases, like in the fiddle you provided; and in another cases, it won't be used.

Add the following to your fiddle to see the different possibilities:

explain select * from `test` where `a` = 'fgsdfgsdfg' and `c` = 'dfsadfsdf'
delete from test where a='dsfasdfsdfasd';
explain select * from `test` where `a` = 'fgsdfgsdfg' and `c` = 'dfsadfsdf';
explain select * from `test` where `a` = 'ABC' and `c` = 'dfsadfsdf';

Notice that in the first scenario, the index was chosen because it will avoid doing a full table scan. In the second scenario, since any way a full table scan will be done, it is more expensive to use the index, so the optimizer decided not to use the "possible" key.

In first scenario, field a's cardinality is 2. i.e. there are two different values of this field in the whole table, and they are 50% each. This means that using the index, only 50% of the table will be scanned, so it is better to use the index.

In the second, the cardinality of field a is 1. i.e. only one distinct value exists in the table, and this is the value I am asking for in the where condition. So, all rows will be scanned. The optimizer decides that is it cheaper to scan that table than to scan the index and the table.

Extra piece of info: In the last statement, I am checking a against a value that doesn't exist. The index will be used in this case.

UPDATE

Composite index on (c1, c2, c3) would sometimes be used if you query based on (c1, c3). It will use the first part (c1) for the speed (to seek the first entry). (c3) part will not increase the speed of seeking, but will make fetching data faster, as it will be fetched from the index in this case.

HTH

Related Question