Use of mysql composite indexes

Recently, I was reading mysql indexes and encountered a problem when using composite indexes: in high-performance mysql, I mentioned: "If a column in a query is a range query, indexes cannot be used for all columns on the right. Wherelast_name & #039; Smith & #039; andfirst_namelike & #039; j % & #039; and...

Recently, I was reading mysql index usage and encountered a problem when using composite indexes:

"If a column in a query is a range query, indexes cannot be used for all columns on the right of the query.
Where last_name = 'Smith 'and first_name like 'J %' and dob = '2017-12-23, this query can only use the first two columns of the index"

In the test, we found that after the like clause is used for range query, indexes can still be used for subsequent columns.

CREATE TABLEt(
c1Char (2) not null default '',
c2Char (2) not null default '',
c3Char (2) not null default '',
c4Char (2) not null default '',
c5Char (2) not null default '',
KEYtest_index(c1,c2,c3,c4)
) ENGINE = MyISAM default charset = utf8
Insertt(c1,c2,c3,c4,c5) Values ('1', '2', '3', '4', '5 ');
Insertt(c1,c2,c3,c4,c5) Values ('A', 'C', 'df', 'D', 'F ');
Insertt(c1,c2,c3,c4,c5) Values ('D', 'x', 'D', 'D', 'x ');
Insertt(c1,c2,c3,c4,c5) Values ('s ','s', 'df ',', 'D ');

EXPLAIN
SELECT * FROM t WHERE c1 = '1' AND c2 = "2" AND c3 LIKE '3% 'AND c4 = '2'

The length of key_len in the test result of the preceding statement is still 24,
However, according to the book, c3 uses like, which is a range search. c4 won't be able to use indexes, but the test result is that C4 can also be used.

What is the reason ???

Reply content:

Recently, I was reading mysql index usage and encountered a problem when using composite indexes:

"If a column in a query is a range query, indexes cannot be used for all columns on the right of the query.
Where last_name = 'Smith 'and first_name like 'J %' and dob = '2017-12-23, this query can only use the first two columns of the index"

In the test, we found that after the like clause is used for range query, indexes can still be used for subsequent columns.

CREATE TABLEt(
c1Char (2) not null default '',
c2Char (2) not null default '',
c3Char (2) not null default '',
c4Char (2) not null default '',
c5Char (2) not null default '',
KEYtest_index(c1,c2,c3,c4)
) ENGINE = MyISAM default charset = utf8
Insertt(c1,c2,c3,c4,c5) Values ('1', '2', '3', '4', '5 ');
Insertt(c1,c2,c3,c4,c5) Values ('A', 'C', 'df', 'D', 'F ');
Insertt(c1,c2,c3,c4,c5) Values ('D', 'x', 'D', 'D', 'x ');
Insertt(c1,c2,c3,c4,c5) Values ('s ','s', 'df ',', 'D ');

EXPLAIN
SELECT * FROM t WHERE c1 = '1' AND c2 = "2" AND c3 LIKE '3% 'AND c4 = '2'

The length of key_len in the test result of the preceding statement is still 24,
However, according to the book, c3 uses like, which is a range search. c4 won't be able to use indexes, but the test result is that C4 can also be used.

What is the reason ???

 c3 LIKE '3%'

Replace thisc3 LIKE '%3'Look!

Like queries are divided into three types:
1.% XXX
2. XXX %
3.% XXX %

Only the second index can be used for the above three types.

Because test_index (c1, c2, c3, c4) is a combination index

I also think this is strange, but mysql will optimize the query of some range, so that the columns after it can also be indexed. But I still don't know when it will be optimized. Think that like also belongs to range, so it may be optimized by mysql. (It is not controllable to optimize this thing, but it should be better)

Are you doing interview questions? Will so many joint indexes be used in real scenarios?