0115 about index realizations

--a little understanding of the index
1 The process of understanding the federated Index, ORDER by, and group by in its entirety
--Change the statement is representative, own analysis, understand why
CREATE TABLE T6 (
C1 CHAR (1) Not NULL DEFAULT ' ',
C2 CHAR (1) Not NULL DEFAULT ' ',
C3 CHAR (1) Not NULL DEFAULT ' ',
C4 CHAR (1) Not NULL DEFAULT ' ',
C5 CHAR (1) Not NULL DEFAULT ' ',
KEY (C1,C2,C3,C4)
) ENGINE INNODB CHARSET UTF8;

INSERT into T6 VALUES (' A ', ' B ', ' C ', ' d ', ' e ');

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c2= ' B ' and c4> ' a ' and c3= ' C ';

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c2= ' B ' and c4= ' a ' ORDER by C3;

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c2= ' B ' and c4= ' a ' ORDER by C5;

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c5= ' a ' ORDER by C2,C3;

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c5= ' a ' ORDER by C3,C2;

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c2= ' B ' and c5= ' a ' ORDER by C2,C3;

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c2= ' B ' and c5= ' a ' ORDER by c3,c2;--[rewritten as Order by C3, ' B '], so directly modified to order by C3, statement write redundancy

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c4= ' d ' GROUP by C2,C3;

EXPLAIN
SELECT * from T6 WHERE c1= ' a ' and c4= ' d ' GROUP by C3,C2;

--sometimes indexes are used but not included in the length of the Key_len, but we find that they distinguish between using where the index is used in extra, but not using the indexed use tempary;using Filsort

Understanding the picture

2 A little bit about explain's own understanding of the rows
-The number of rows that need to be scanned, representing the
EXPLAIN
SELECT * from Ta WHERE rid1=429 and rid2=36

SELECT * from Ta WHERE rid2=36--12
--equivalent to finding 12 rows of qualifying statements by index, so the number of rows scanned is definitely 12, and then the back table uses where to filter to get the final 6
-So building better indexes can reduce the number of scanned rows, the index function greatly

The execution process of an SQL statement

3 about how MySQL is structured by the group by analysis

Therefore, to establish an effective index, you can avoid using Tempary; Using Filesort;

0115 about index realizations