SQL statement optimization for MySQL

For the brothers who often use MySQL, the explain must not be unfamiliar. When you put a keyword in front of a SELECT statement Explain,mysql explains how it will handle select, providing information about how the table is federated and in what order.
With the help of explain, you can know
1) When do you have to index the table to get a faster select that uses the index to find records.
2) You can also see if the optimizer joins the table in the best order. To force the optimizer to use a specific junction order for a SELECT statement, add a straight_join clause.
The official document on explain in http://dev.mysql.com/doc/refman/5.1/en/using-explain.html (English), this article can be used as an incomplete popular writings of official documents.

the method used

EXPLAIN Tbl_name
Or:
EXPLAIN [EXTENDED] SELECT select_options

The former can draw a table of the field structure and so on, the latter is mainly to give some relevant index information, and today the focus is the latter.

Example

mysql> Explain select * from event;
+----+-------------+-------+------+---------------+------+---------+------+ ------+-------+
| ID | select_type | table | type | possible_keys | key | key_len | ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+ ------+-------+
| 1 | Simple | Event | All | NULL | NULL | NULL | NULL | 13 | |
+----+-------------+-------+------+---------------+------+---------+------+ ------+-------+
1 row in Set (0.00 sec)

the meaning of each property

Id
Serial number of the select query

Select_type
The type of select query is mainly the difference between common queries and complex queries such as federated queries and subqueries.

Table
The table that is referenced by the output row.

Type
The type used by the union query.
Type shows the type of access, which is an important indicator, and the resulting values from good to bad are:
System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All
In general, it is best to ensure that the query reaches at least the range level, preferably ref.

Possible_keys
Indicates which index MySQL can use to find rows in the table. If it is empty, there is no index associated with it. To improve performance, you can examine where clauses to see if some fields are referenced, or check that the fields are not appropriate for the index.

Key
Displays the keys that MySQL actually decides to use. If no index is selected, the key is null.

Key_len
Displays the key lengths that MySQL decides to use. If the key is null, the length is null. Documentation tips pay particular attention to this value to derive a multi-primary key in what part of MySQL is actually used.

Ref
Shows which field or constant is used together with the key.

Rows
This number indicates how much data MySQL will traverse to find and is inaccurate on InnoDB.

Extra
If it is only index, this means that information is retrieved only from the information in the index tree, which is faster than scanning the entire table.
If it is a where used, the where limit is used.
If it is impossible where means no where, it is generally not found out what.
If this information shows the using Filesort or using temporary, then the where and order by indexes are often out of balance, and if the index is determined by where, then the order by will inevitably cause the using Filesort, it depends on whether to filter and reorder the cost, or first sort and then filter the cost.

Some common noun explanations

Using Filesort
MySQL requires an extra pass to find out how rows are retrieved in sorted order.

Using Index
Retrieves column information from a table by using only the information in the index tree without requiring a further search to read the actual rows.

Using Temporary
To resolve the query, MySQL needs to create a temporary table to accommodate the results.

Ref
For each row combination from the preceding table, all rows with matching index values are read from this table

All
With no index at all, the performance is very poor.

Index
Same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.

Simple
Simple select (Do not use union or subquery)

SQL statement optimization for MySQL