MySQL in explain command detailed

Source: Internet
Author: User

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:

EXPLAIN SELECT ' surname ',' first_name 'a ',' B 'WHERE' a '. ' id '=' B '. ' id '

Explanation of the EXPLAIN column:

column Description
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, index, and all.
Possible_keys Displays the indexes that may be applied in 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 index that is actually 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 to use. Without loss of accuracy, the shorter the better.
Ref Shows which column of the index is used and, if possible, a constant.
Rows MySQL considers the number of rows that must be checked to return the requested data.
Extra Additional information about how MySQL parses the query. will be discussed in Table 4.3, but the bad examples you can see here are using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be slow.

The extra column returns the meaning of the description:


value meaning
Distinct Once MySQL finds a row that matches the row, it no longer searches.
NOT EXISTS MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches.
Range checked for each Record (index map:#) The ideal index was not found, so for each combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections that use the index.
Using Filesort When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.
Using Index Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request 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, which usually happens on the order by for different sets of columns, rather than on the group by.
Where used A WHERE clause is used to restrict which rows will match the next table or are returned to the user. If you do not want to return all the rows in the table, and the connection type all or index, this occurs, or the query has a problem with the interpretation of the different connection types (sorted in order of efficiency).
System The table has only one row of system tables. This is a special case of the const connection type.
Const The maximum value of a record in a table matches the 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 reads the value first and treats it as a constant.
Eq_ref In the connection, when MySQL queries, from the previous table, the union of each record reads a record from the table, which is used when the query uses all indexes as primary or unique keys.
Ref This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better.
Range This connection type uses the index to return rows in a range, such as what happens when you use > or < to find something.
Index This connection type makes a full scan of each record in the previous table (better than all, because the index is generally less than the table data).
All This connection type is completely scanned for each previous record, which is generally bad and should be avoided as much as possible.

MySQL in explain command detailed

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.