Problems with the MySQL composite index

Recently, when I looked at MySQL's index usage, I encountered a problem in the composite index:

In high-performance MySQL, "If a column in a query is a range query, all columns on its right cannot use the index."
where Last_name= ' Smith ' and first_name like ' j% ' and dob= ' 1976-12-23, this query can only use the first two columns of the index "

After you have found a scope query using like, the following columns can still use the index

CREATE TABLE t (
C1 char (2) NOT null DEFAULT ",
C2 char (2) NOT NULL Defau LT ',
c3 char (2) NOT null default ",
c4 char (2) NOT null default ',
c5 char (2) Not NULL DEFAULT ' ',
KEY test_index ( C1 , C2 , c3 , C4 )
) Engine=myisam DEFAULT Charset=utf8
INSERT into t ( C1 , C2 , Code>c3 , c4 , c5 ) VALUES (' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 ');
INSERT INTO t ( C1 , C2 , c3 , c4 , c5 ) VALUES (' A ', ' C ', ' df ', ' d ', ' f ');
INSERT INTO t ( C1 , C2 , c3 , c4 , c5 ) VALUES (' d ', ' X ', ' d ', ' d ', ' X ');
INSERT INTO t ( 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 result of the above statement test is still 24, the length of the Key_len
But according to the book said C3 use like, belong to a range of search, C4 can not use the index is right, but the test result is C4 can also use

Excuse me, what is the reason for this???

Reply content:

Recently, when I looked at MySQL's index usage, I encountered a problem in the composite index:

In high-performance MySQL, "If a column in a query is a range query, all columns on its right cannot use the index."
where Last_name= ' Smith ' and first_name like ' j% ' and dob= ' 1976-12-23, this query can only use the first two columns of the index "

After you have found a scope query using like, the following columns can still use the index

CREATE TABLE t (
C1 char (2) NOT null DEFAULT ",
C2 char (2) NOT NULL Defau LT ',
c3 char (2) NOT null default ",
c4 char (2) NOT null default ',
c5 char (2) Not NULL DEFAULT ' ',
KEY test_index ( C1 , C2 , c3 , C4 )
) Engine=myisam DEFAULT Charset=utf8
INSERT into t ( C1 , C2 , Code>c3 , c4 , c5 ) VALUES (' 1 ', ' 2 ', ' 3 ', ' 4 ', ' 5 ');
INSERT INTO t ( C1 , C2 , c3 , c4 , c5 ) VALUES (' A ', ' C ', ' df ', ' d ', ' f ');
INSERT INTO t ( C1 , C2 , c3 , c4 , c5 ) VALUES (' d ', ' X ', ' d ', ' d ', ' X ');
INSERT INTO t ( 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 result of the above statement test is still 24, the length of the Key_len
But according to the book said C3 use like, belong to a range of search, C4 can not use the index is right, but the test result is C4 can also use

Excuse me, what is the reason for this???

 c3 LIKE '3%'

You change this to c3 LIKE '%3' look!

Like query is divided into 3 kinds
1.%xxx
2.xxx%
3.%xxx%

Only the second of the above 3 types can use the index

Because Test_index (C1,C2,C3,C4) is a composite index

I also think it's strange, but MySQL will optimize the query for part of range, so that the columns that follow it can also be indexed. But I don't know when it's going to be optimized. Think like also belongs to range, so it may also be optimized by MySQL. (To optimize this thing is not controllable, or strictly point good)

Do you have a face test? Does the real scene use so many federated indexes?

