MySQL Select Optimization

Get ready:

CREATE table t (x int primary key,y int unique,z int);

INSERT into t (x, y, z) VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5), (6,6,6), (7,7,7), (8,8,8), (9,9,9);

Condition 1:select not used in index

Explain select Z from T;

　　

Type = All indicates a full-table scan, which means that the data in the table is read once to get the result, and this query is usually very slow. It is much better to add a matching index to the query.

Scenario 2: Using the index

Select Y from T;

　　

Type = index indicates that the query is indexed, compared to Type=all because it does not load the entire table into memory, as long as the index can be loaded to complete the query, so that the amount of data read less. It's usually faster than Type=all.

Scenario 3:

The index is used, but only a portion of the index can be loaded to complete the query

Select min (y), Max (y) from T;

　　

Compared to case 2, it's more table=null, and it doesn't need to look inside the table, type=null it doesn't look like it's indexed, as if it were taken from the counter. But I think it's gone. The index is just a small part of the index. Note Its extra description "Select tables optimized Away"

Scenario 4:

The Where condition has an index available, select executes Count,sum

Select count (y), sum (y) from T where y>3;

　　

The key = y description uses index y, type = range to indicate that it does not need to load the entire index, as long as it is loaded in part (Y>3).

Scenario 5:

The where segment has a primary key index available, and the Select segment is an aggregate function

Select SUM (x), Min (x), Max (x), count (x) from T where x>3;

　　

Here I have a problem is that the case 5 of Key_len is 4 in case 4 Key_len is 5 but x, y can all be int type Ah!

　　

MySQL Select Optimization