MySQL in explain detailed

MySQL Explain detailed 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: ExplainSelectUsername,first_name form Hx,itlearnerwherea.ID=explanation of b.ID explain column: ID: this timeSelectidentifier. Each select in the query has a sequential numeric value. Select_type: Query type, more simple, easy queryTable: Displays the data for this row about which table is of type: This is an important column that shows what type of connection is used. The best to worst connection types are const, EQ_REF, ref, range, Indexhe, and Allpossible_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 domainKey: 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 can be used in the SELECT statementINDEX(IndexName) to force an index or use ignoreINDEX(indexname) to force MySQL to ignore index Key_len: The length of the index used. Without loss of accuracy, the shorter the better ref: which column of the display index is used, if possible, is a constant rows:mysql the number of rows that must be checked to return the requested data extra: Additional information about how MySQL parses the query. The worst example is using temporary and using filesort, meaning that MySQL simply cannot use the index, and the result is that the retrieval will be slow. Select_type Detailed: simple: EasySelect(no union or subquery is used)Primary: the outermostSelect. Union: The second layer, after select, uses theUnion. DependentUnion:Unionthe second select in the statement, dependent on the outer subquery subquery: The first in a subquerySelectDependent subquery: The first subquery in a subquery relies on an external subquery derived: derived tablesSelect(subquery in FROM clause) type is detailed: The system table has only one row: system table. This is a special case of a const connection type const: The maximum value of one record in a table can match 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 this value first and treats it as a constant: In the connection, MySQL reads a record from the table in the previous table, and the union of each record in the Eq_ref. It uses ref when the query uses all indexes as primary or unique keys: This connection type occurs only if the query uses keys that are not unique or primary keys, or parts of those 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 match the index-the less the better range: This connection type uses the index to return rows in a range, such as using>Or<What happens when I look for somethingIndex: 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 has a full scan of each previous record joint, which is generally bad and should avoid the meaning of the description returned by the extra columnDistinct: Once MySQL finds a row that matches a row, it no longer searches for not exists: MySQL Optimized leftJOIN, once it finds a row that matches the left join standard, it no longer searches for range checked forEach Record (Indexmap:#): No ideal index was found, so for each row combination from the previous table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to 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 using the connection type and the row pointers for all rows that store the sort key values and matching criteriaIndex: 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 the entire request column of the table is part of the same indexTemporarywhen you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group byWhereUsed uses a WHERE clause to restrict which rows will match the next table or return to the user. If you do not want to return all rows in the table, and the connection type all or index, this occurs, or the query has a problem different connection types of interpretation (in order of efficiency)

MySQL in explain detailed