Explain commands in MYSQL (copied)

For large data volume of query processing, remember to use Explian to see, as far as possible optimization

For example: Explain select surname,first_name form A, a where a.id=b.id

Explain Explanation:

Table: The data that displays this line is about the table.

Type: This is an important column that shows what type of connection is used. The best to worst link type is Const–eq_reg–ref–range–indexhe–all

Possible_keys: Displays the index that may be applied to this table. If it is empty, there is no possible index. You can select the 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 IGNORE index (indexname) to force MySQL to ignore the index

Key_len: The index length used. Without loss of precision, the shorter the better

Ref: The column that displays the index is used and, if possible, a constant.

ROWS:MYSQ the number of rows that must be checked to return the requested data.

Extra: Additional information about how MYSQ resolves the query. (Using Temporary | Using filesort meaning MySQL cannot use index at all)

The meaning of the description returned by the--extra column

Distinct: Once MySQL finds a row that matches a row, it no longer searches for

Not Exists:mysql optimizes the left join, and once a matching left join standard row is found, no longer search

Range checked for each Record (index map:#): No ideal index was found, so for every 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 links 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 based on the row pointer of all rows that the link type has stored the sort key value and matching criteria.

Using index: The row data is returned from a table that uses only the information in the index and does not read the actual action. This occurs when all requests 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 occurs on the order by of the different column sets, not on the group by.

Where used: A WHERE clause is used to restrict which rows will match the next table or returned to the user. If you do not want to return all rows in the table, and the link type All or index, this occurs, or the query has a problem the interpretation of the different link types (sorted in order of efficiency)

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

Const: The maximum value of a record in a table 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 reads the value first and treats it as a constant.

Eq_res: 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 the index as the primary key or the unique key

Ref: Song link 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 the number of records that match the index: The less--------, the better.

Range: This link type uses the returned row returned by the index. Like what happens when you use > or < to find something

Index: This connection type is fully scanned for 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.

Explain commands in MYSQL (copied)