2

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)

3
  • 1
    Will the composite index be used in case of WHERE a = const AND c = const condition? This index can be used for WHERE a = const condition part only as for index scan and as a covering for the rest. It will be used if its cardinality is high enough (less than 5-7% approx.) or if it is covering index for the whole query, and may be used or not used in another cases.
    – Akina
    Commented Mar 14, 2019 at 7:00
  • Thanks @Akina! That was my question. Obviously it could only be used (if it could be at all!) for the a = const part of WHERE clause, but not for the ones going after "missing" columns from the index order. Do you have any trusted sources or perhaps (unlikely as it's really a huge part of optimizer functionality) reference to mysql source where the optimizer decides, whether to use it or not.
    – Alexey
    Commented Mar 14, 2019 at 7:36
  • but not for the ones going after "missing" columns from the index order. No. Read my comment carefully about covering. It it possible that index will be used not like index but like compact variant of a table (compare fullscans by huge table and compact index) - it can increase performance.
    – Akina
    Commented Mar 14, 2019 at 7:49

2 Answers 2

2

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

2
  • Thanks @Jehad! As for the cardinality of a column and general decisions on full table scan and whether to use an index or not - this functionality is common for both single-column as well as composite indexes, to my mind. See my question edit, I'm primarily interested in just possibility to use a composite index for the part of WHERE clause that does not violate the column order. So whether it could be used in the following case: index(c1,c2,c3,c4,c5) / WHERE c1=const AND c2=const AND c5=const, will it be used for the part of c1=const AND c2=const assuming the cardinality is high
    – Alexey
    Commented Mar 14, 2019 at 7:42
  • Updated the answer Commented Mar 14, 2019 at 21:16
0

If would be better to use separate index for each column.

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

Usually composite index is used to enforce unique combinations and composite primary keys.

If you expect to frequently search WHERE a = const AND b = const AND c = const, then it's also useful.

Your composite index should help with the a = const part in WHERE a = const AND c = const, but not with c.

Basically, if your index is:

INDEX(a,b,c,d)

if can be used for the following searches:

 - WHERE a = const AND b = const AND c = const AND d = const
 - WHERE a = const AND b = const AND c = const
 - WHERE a = const AND b = const
 - WHERE a = const
6
  • Thanks, but the question is not in how to better design the table structure/indexes, I'm only interested in how composite index works or doesn't work for conditions using only part of it missing some columns in the middle of the index. I.e. 1st and 3rd columns in the index order as in the example above.
    – Alexey
    Commented Mar 13, 2019 at 19:36
  • Are you just exploring and testing? Commented Mar 13, 2019 at 20:05
  • yes, exactly, trying to figure out how composite indexes work + how mysql optimizer works in case of AND conditions on parts of the index that are not in sequential order from the index itself
    – Alexey
    Commented Mar 13, 2019 at 20:06
  • @Alexey, I added more info. Commented Mar 13, 2019 at 21:17
  • yes, that's what I'm interested in ,but would appreciate sources, if you found a confirmation that it will use it for the first part of WHERE clause. Where did you find the info?
    – Alexey
    Commented Mar 13, 2019 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.