MySQL Learning note (vi) Explain analysis query

Using explain, you can emulate the optimizer's execution of SQL statements to understand how MySQL handles your SQL and analyze the bottleneck of query statements or table structures.

By Expain you can get:

1) Table: Shows which table the data for this line is about.

2) Type: shows what type the query uses. The best to worst connection types are system, const, EQ_REG, ref, range, index, and all.

3) Possible_keys: Displays the index that may be applied in this table. If it is empty, there is no possible index.

4) Key: The actual index used. If NULL, the index is not used. MySQL rarely chooses to optimize poorly indexed indexes, which can be used to force an index or use ignore index to force an index to be ignored in a SELECT statement.

5) Key_len: The length of the index used. Without loss of accuracy, the shorter the better.

6) Ref: shows which column of the index is used and, if possible, a constant.

7) Rows:mysql The number of rows that must be retrieved to return the requested data.

8) Extra:

Using where, using Index,using filesort,using index for group-by

MySQL Learning note (vi) Explain analysis query