Detailed MySQL explain interpretation commands

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements.

Using the method, add explain to the SELECT statement. Such as:

Select

Output similar to the following

Explanation of the Explain column:

table: Shows which table the data for this row is about

type: This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all

Possible_keys: Displays the indexes that may be applied to this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain

key: The actual index used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use index(indexname) can be used in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index

Key_len: The length of the index used. The shorter the length the better, without loss of accuracy

ref: Shows which column of the index is being used and, if possible, a constant

rows: The number of rows that MySQL considers must be checked to return the requested data

Extra: Additional information on how MySQL resolves queries. It will be discussed in Table 4.3, but the bad examples you can see here are the using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be slow

Detailed MySQL explain interpretation commands