Parsing the EXPLAIN command in MySQL

explain shows mysqlhow to use indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements.

How to use it, just add explain before the select statement:
For example:

explain select surname,first_name form a,b where a.id=b.id

Explanation of EXPLAIN column:

table: Shows which table this row of data is about

type: This is an important column, showing what type of connection is used. The join types from best to worst are const, eq_reg, ref, range, indexhe and ALL

possible_keys: Displays the indexes that may be applied to this table. If empty, no index is possible. An appropriate statement can be selected from the WHERE statement for the relevant domain

key: The actual index used. If NULL, no index is used. Rarely, MYSQL will select an index that is under-optimized. In this case, you can use USE INDEX (indexname) in the SELECT statement to force the use of an index or IGNORE INDEX (indexname) to force MYSQL to ignore the index

key_len: The length of the index used. Without losing accuracy, the shorter the length, the better

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

rows: MYSQL thinks it must be checked The number of rows used to return the requested data

Extra: Extra information about how MYSQL parses the query. Will be discussed in Table 4.3, but bad examples that can be seen here are Using temporary and Using filesort, meaning MYSQL cannot use the index at all, and the result is that the retrieval will be very slow

Extra column description returned Meaning

Distinct: Once MYSQL finds a row that matches the row union, it no longer searches for it

Not exists: MYSQL optimizes LEFT JOIN once it finds a match LEFT JOIN standard rows are no longer searched.

Range checked for each Record (index map:#): The ideal index is not found, so for each row combination from the previous table, MYSQL checks Which index to use and use it to return rows from the table. This is one of the slowest connections using an index

Using filesort: When you see this, the query needs to be optimized. MYSQL requires an extra step to discover how to sort the returned rows. It sorts all rows based on the connection type and the row pointers that store the sort key value and all rows matching the condition

Using index: Column data is obtained from just using the information in the index without reading the actual action The table is returned. This occurs when all requested columns of the table are part of the same index.

Using temporary When you see this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually occurs when ORDER BY is performed on different column sets instead of GROUP BY.

Where used uses a WHERE clause to limit which rows will be compared with the next Table matches or is returned to the user. This can happen if you do not want to return all the rows in the table and the join type is ALL or index, or there is a problem with the query. Explanation of different join types (sorted in order of efficiency)

system The table has only one row: system table. This is a special case of the const connection type

const: The maximum value of a record in the table that can match this query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MYSQL first reads this value and then treats it as a constant

eq_ref: In the connection, MYSQL will query each record from the previous table The join reads a record from the table. It is used when the query uses all of the index's primary key or unique key.

ref: This connection type is only used when the query uses a key that is not a unique or primary key or is These types of sections (e.g., utilizing the leftmost prefix) occur. For each row join of the previous table, all records will be read from the table. This type relies heavily on how many records are matched based on the index - the fewer the better

range: This join type uses the index to return a range of rows, such as what happens when using > or
index: This connection type performs a complete scan of each record union in the previous table (better than ALL, because the index is generally smaller than the table data)

ALL: This connection type performs a complete scan of each record in the previous table Each record is combined for a full scan, which is generally bad and should be avoided

The above is the detailed content of Parsing the EXPLAIN command in MySQL. For more information, please follow other related articles on the PHP Chinese website!