[SQL]View PlainCopyPrint?
- explain Select * from user
Explain select * from user
[SQL]View PlainCopyPrint?
- Explain extended Select * from user
Explain extended select * from user
| Id |
Select identifier. This is the query sequence number for select |
| Select_type |
Select type, which can be any of the following:
- Simple : Simple Select (Do not use union or subquery)
- PRIMARY : The outermost select
- UNION : The second or subsequent SELECT statement in a Union
- DEPENDENT UNION : The second or subsequent SELECT statement in the Union, depending on the outside query
- UNION RESULT : Result of UNION
- subquery : The first select in a subquery
- DEPENDENT subquery : The first select in a subquery, depending on the outside query
- DERIVED : Export table's SELECT (subquery FROM clause)
|
| Table |
Table referenced by the output row |
| Type |
The join type. Here are the various join types, sorted by best type to worst type:
- system : The table has only one row (= system table). This is a special case of the const join type.
- Const : The table has a maximum of one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is fast because they are read only once!
- Eq_ref : Reads a row from the table for each row combination from the preceding table. This may be the best type of join, except for the const type.
- ref : For each row combination from the preceding table, all rows with matching index values are read from this table.
- Ref_or_null : The join type is like ref, but the addition of MySQL allows you to specifically search for rows that contain null values.
- Index_merge : The join type represents the use of the index merge optimization method.
- Unique_subquery : The type replaces the following form of in subquery Ref:value in (the SELECT primary_key from single_table WHERE some_expr) unique_subquery is an index lookup function, It is more efficient to completely replace the subquery.
- Index_subquery : The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in subqueries of the following form: value in (SELECT key_column from single_table WHERE some_expr)
- Range : Retrieves only the rows for a given range, using an index to select rows.
- Index : The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.
- All : A complete table scan for each row combination from the previous table.
|
| Possible_keys |
Indicates which index MySQL can use to find rows in the table |
| Key |
Displays the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. |
| Key_len |
Displays the key lengths that MySQL decides to use. If the key is null, the length is null. |
| Ref |
Shows which column or constant is used together with key to select rows from the table. |
| Rows |
Shows the number of rows that MySQL must check when it executes a query. Data multiplication between multirow can estimate the number of rows to be processed. |
| Filtered |
Shows the percentage estimate of the number of rows filtered through the condition. |
| Extra |
This column contains the details of the MySQL resolution query
- Distinct : After MySQL finds the 1th matching row, it stops searching for more rows for the current row combination.
- Not exists : MySQL was able to perform a left join optimization on the query, and after discovering 1 rows that matched the left join standard, no more rows were checked within the table for the preceding row.
- range checked for each record (Index map: #) : MySQL did not find a good index to use, but found that some indexes could be used if the column values from the previous table were known.
- Using Filesort : MySQL requires an extra pass to find out how rows are retrieved in sorted order.
- Using Index : Retrieves column information from a table by using only the information in the index tree without requiring a further search to read the actual rows.
- Using Temporary : To resolve the query, MySQL needs to create a temporary table to accommodate the results.
- Using where : The WHERE clause is used to restrict which row matches the next table or send to the customer.
- using Sort_union ( ...), using Union (...), using intersect (...) : These functions describe how to merge index scans for Index_merge join types.
- Using index for group-by : Similar to accessing a table using the index method, using index for group-by means that MySQL has found an index that can be used to query all columns of a group by or distinct query, and not to search the hard disk for access to the actual table.
|
The meaning of explain usage and results in MySQL
