Source: Internet
Author: User

--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;

Related Keywords:

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.