EXPLAIN commands in MySQL

The EXPLAIN command in MySQL shows how mysql uses indexes to process select statements and connect tables. It can help you select better indexes and write more optimized query statements. You can use the explain statement before the select statement, for example, explain select surname, first_name form a, B where. id = B. description of the id EXPLAIN column: table: the type of the table in which the data in this row is displayed: this is an important column and shows the type used by the connection. The best to worst connection types are const, eq_reg, ref, range, indexhe, and ALL possible_keys: displays indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement key from the WHERE statement for the relevant domain: the actually used index. If it is NULL, no index is used. In rare cases, MYSQL selects an optimized index. In this case, you can use index (indexname) 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. ref: displays which column of the index is used. If possible, it is a constant rows: MYSQL considers that the number of rows that must be checked to return the request data Extra: Additional information about how MYSQL parses the query. We will discuss it in table 4.3, but here we can see that the bad examples are Using temporary and Using filesort, which means MYSQL cannot use indexes at all, the result is that the search will be slow. The description returned by the extra column is meaningful. Distinct: Once MYSQL finds the row that matches with the row, it does Not search for Not exists: MYSQL optimizes left join, once it finds a row that matches the left join standard, it no longer searches for Range checked for each Record (index map: #): it does not find the ideal index, therefore, for each row combination in the preceding table, MYSQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections Using indexes, Using filesort: When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts the Using index of all rows based on the connection type and the row pointer of all rows that store the sort key value and match the condition: column data is returned from a table that only uses the information in the index but does not read the actual action, this occurs when all the request columns of the table are the same index, and Using temporary sees this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually happens when order by is performed on different column sets, instead of using the Where clause on group by, WHERE used uses the Where clause to restrict which rows match the next table or which rows are returned to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order) the system 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 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 treats it as a constant for eq_ref: During the connection, MYSQL queries from the previous table, the Union of each record reads a record from the table. It uses ref when querying all the records whose indexes are primary keys or unique keys: this connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the less the better the range: This connection type uses the index to return rows in a range, such as> or <what happens when something is searched index: this connection type performs a full scan of each record in the previous table (better than ALL, because the index is generally smaller than the table data) ALL: this connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.