MySQL's explain command

The explain command is the primary way to see how the query optimizer decides to execute a query. Call EXPLAINTo use explain, simply add explain before the SELECT keyword in the query. MySQL sets a tag on the query. When a query is executed, the token returns information about each step in the execution plan, rather than executing it (in general). It returns one or more rows of information, showing each part of the execution plan and the order in which it is executed. Explain select 1 Results example: In the query, each table has only one row in the output. If the query is a two-table connection, there will be two rows in the output. Alias forms are counted as a single table. If you condense a table with itself, there will be two lines in the output. The meaning of "table" is quite extensive here: it can be a subquery, a union result, and so on. There are two main variants of EXPLAIN:

• EXPLAIN EXTENDED. It notifies the server that the "reverse compilation" Execution plan is a SELECT statement. You can see this generated statement by running show warnings immediately thereafter. This statement comes directly from the execution plan, not the original SQL statement, which has become a data structure. In most scenarios it is not the same as the original statement. (The Filterd column is added to the result output)

• EXPLAIN partitions. It displays the partition that the query will access if the query is based on a partitioned table. (The Partitions column is added to the result output)

It is a common mistake to think that MySQL will not execute queries when adding explain. If the query includes subqueries in the FROM clause, then MySQL will actually execute the subquery, place its results in a temporary table, and complete the outer query optimization. It must handle all similar subqueries before it can complete the outer query optimization. Explain is just an approximate result. The following are some of the relevant limitations:

• Explain doesn't tell you how triggers, stored procedures, or UDFs affect queries.
• EXPLAIN does not support stored procedures, although you can manually extract queries and EXPLAIN them individually.
• Explain will not tell you the specific optimizations that MySQL has made in query execution.
• Explain does not display all information about the execution plan for the query.
• Explain does not distinguish between things that have the same name. For example, it uses "Filesort" for both memory ordering and temporary files, and displays "Using temporary" for temporary tables on disk and in memory.

columns in the EXPLAINThe output columns of the explain include: ID, select_type, table, type, Possible_keys, key, Key_len, ref, rows, extra, and so on. ID ColumnThis column always contains a number that identifies the row to which the select belongs. If there are no subqueries or unions in the statement, there will only be a unique select, and each row will display a 1 in that column. Otherwise, the inner SELECT statement is typically numbered sequentially, corresponding to its position in the original statement. MySQL divides select queries into simple and complex types, and complex types can be categorized into three main categories: simple subqueries, so-called derived tables (subqueries in the FROM clause), and union queries. Note the additional rows in the union result output. The union result is always placed in an anonymous temporary table, after which MySQL reads the result out of the temporary table. The temporary table does not appear in the original SQL, so its ID column is null. select_type ColumnThis column shows whether the corresponding row is a simple or complex select. The values are as follows:

Simple	The query does not include subqueries and unions.
PRIMARY	The outermost row of the complex select.
Subquery	A select (simple subquery) that is contained in a subquery in the select list. Subquery can also be marked as dependent,dependent subquery means that select relies on the data found in the outer query.
DERIVED	A select that is contained in a subquery in the FROM clause. MySQL executes recursively and puts the results into a temporary table. The server internally calls its "derived table" because the temporary table is derived from the subquery.
UNION	The second and subsequent select in the Union. The first select is marked as if it were executed in a partial out-of-order query. If the union is contained by a subquery in the FROM clause, then its first select is marked as derived. The Union can also be marked as uncacheable. Uncacheable UNION means that some features in select prevent the results from being cached with a item_cache.
UNION RESULT	A select that is used to retrieve the results from an anonymous temporary table in union.

table ColumnThis column shows which table the corresponding row is accessing. You can see from top to bottom in this column the Association optimizer of MySQL selects the associated order for the query. Query execution is scheduled in the same way as the rows in explain:   derived tables and unionsTable columns can become much more complex when there are subqueries or unions in the FROM clause. In these scenarios, there is really no "table" to refer to because the anonymous temporary table created by MySQL only exists during query execution. When there is a subquery in the FROM clause, the table column is in the form of <derivedN>, where n is the ID of the subquery. This is always "forward reference". In other words, n points to the following line in the explain output. When there is a union, the table column of union result contains a list of IDs that participate in the Union. This is always "backward-referencing" because union result appears after all the participating rows in the union. Type column  The type column shows the types of MySQL access, that is, MySQL determines how to find rows in a table. The following are important access methods, from worst to best:

All	A full table scan usually means that MySQL must scan the entire table, from beginning to end, to find the desired line. There is an exception to this, such as using limit in a query, or displaying "using Distinct/not exists" in the extra column.
Index	This is the same as a full-table scan, except that the MySQL scan table is in indexed order instead of rows. Its main advantage is to avoid sorting, and the biggest disadvantage is to assume the cost of reading the entire table in indexed order. This usually means that if the rows are accessed in random order, the overhead will be very large. If you see "Using index" in the extra column, it means that MySQL is using the overwrite index, which scans only the indexed data, not each row in the index order. It costs a lot less than full table scans in indexed order.
Range	Range Scan a restricted index scan that starts at a certain point in the index and returns rows that match that value. This is better than a full index scan because it does not need to traverse all indexes. The obvious range scan is a query with a between or a > in the WHERE clause. When MySQL uses an index to find a range of values, such as in () and or lists, it is also displayed as a range scan. However, the two are actually quite different types of access, with major differences in performance. The cost of such a scan is equivalent to the index type.
Ref	This is an indexed access (sometimes called an index lookup), which returns all matches to a single worthwhile row. However, it may find multiple rows that match the criteria, so it is a mixture of lookups and scans. This type of index access occurs only if a non-unique prefix that uses a non-unique index or a uniqueness index is used. It is called ref because the index is compared to a reference value. This reference value is either a constant or a result value from the previous table in a multi-sheet query. Ref_or_null is a variant above ref, which means that MySQL must be the result of the initial lookup.
Eq_ref	With this index lookup, MySQL knows that only one record that matches the criteria is returned. This access method can be seen when MySQL uses a primary key or a unique index lookup, which compares them to a reference value. MySQL sits very well for this type of access, because it knows that there is no need to estimate the range of matching rows or to continue looking after matching rows are found.
Const, System	When MySQL can optimize a part of a query and convert it to a constant, it uses these types of access. For example, if you select the primary key for this row by placing the primary key of a row in the WHERE clause, MySQL can convert the query to a constant. You can then effectively remove the table from the join execution.
Null	This access means that MySQL can break down query statements during the optimization phase, or even need to access tables or indexes at the execution stage. For example, selecting the minimum value from an index column can be done by looking up the index separately, without having to access the table when it is executed.

  possible_key Column  This column shows which indexes the query can use, based on the columns that the query accesses and the comparison operators that are used. This list is created early in the optimization process, so some of the listed indexes may not be useful for subsequent optimization processes. key ColumnThis column shows which index MySQL decides to use to optimize access to the table. If the index does not appear in the Possible_key column, then MySQL chooses it for another reason. For example, it may have chosen an overwrite index, even without a WHERE clause. Possible_key reveals which indexes can be useful for efficient row lookups, while key shows which indexes are optimized to minimize the cost of finding. Key_len ColumnThis column shows the number of bytes that MySQL uses in the index. If MySQL is using only some of the columns in the index, then this value can be used to figure out which columns are specific. The Key_len column shows the maximum possible length in the indexed field, not the actual number of bytes used in the data in the table. ref columnThis column shows the columns or constants used by the previous table to look up values in the index of the key column record. rows ColumnThis column is the number of rows that MySQL estimates to read in order to find the desired row. This data is the number of loops in the inline loop associated plan. In other words, it is not the number of rows that MySQL thinks it will eventually read from the table, but the average of the rows that MySQL must read in order to find the rows that meet the criteria at each point in the query. (This standard includes the conditions given in SQL, and the current column from the previous table in the join order). This number is the number of rows that MySQL thinks it will check, not the number of rows in the result set. Extra ColumnThis column contains additional information that is not suitable for display in other columns. The most important values that are common are as follows:

Using Index	This value indicates that MySQL will use the overwrite index to avoid accessing the table. Do not confuse the overlay index with the index access type.
Using where	This means that the MySQL server will filter after the storage engine retrieves the rows. Many where conditions involve columns in the index, and when (and if) it reads the index, it can be inspected by the storage engine, so not all queries with a WHERE clause will display a "Using where". Sometimes the presence of a "Using where" is a hint: A query can benefit from a different index.
Using Temporary	This means that MySQL uses a temporary table when sorting the results of the query.
Using Filesort	This means that MySQL sorts the results by using an external index instead of reading rows from the table in the index order. MySQL has two kinds of file sorting algorithms. Both methods can be done on memory or on disk. But explain doesn't know which sort of file MySQL will use, nor does it tell you whether the sort will be done in memory or on disk.
Range checked for each record (index map:n)	This value means that there is no good index, and the new index will be recalculated on each line of the join. N is a bitmap that is displayed in the index of the Possible_keys column and is redundant.

Filtered ColumnThis column appears when you use explain extended. It shows a pessimistic estimate of the percentage of records in the table that conform to a condition (WHERE clause or join condition). If you multiply the rows column with this percentage, you can see the number of rows that MySQL estimates will be associated with the previous table in the query plan.  

MySQL's explain command