MySQL Execution analysis

Select_type: represents the type of select, the following are common

Simple table, which does not use a connection or subquery

PRIMARY: main query, which is the outer query

UNION: The second or subsequent query statement in a union

subquery: The first select in a subquery

Table : tables for output result sets

Type : represents the connection type for the table, with the following sequence of performance from good to Bad

System: There is only one row in the table, which is the constant table

Const: There is a maximum of one matching row in a single table, such as primary key or unique index

eq_ref: for each preceding row, only one record is queried in this table, that is, primary key or unique index is used in a multi-table connection

ref: Similar to Eq_ref, the difference is that instead of using primary key or unique index, you use a normal index

Ref_or_null: with ref type, except that the condition contains a null query

index_merge: Index Consolidation optimization

unique_subquery: in is followed by a subquery that queries the primary key field

index_subquery: similar to Unique_subquery, except that in is followed by subqueries that query for non-unique indexed fields

Range : scope query in a single table

Index : for each preceding row, the data is obtained by querying the index

All : for each previous row, the data is scanned by the full table

Possible_keys: indexes that may be used when querying

key: The index that was actually used when querying

Key-len: Length of index field

rows: number of scanned rows

Extra: Description and description of the implementation

For example:

EXPLAIN SELECT SUM (money) from sales S,company C WHERE s.company_id=c.id and s.year=2006 \g;

1. Row ***************************
Id:1
Select_type:simple
Table:s
Type:ref
Possible_keys:index_seles_year
Key:index_sales_year
Key_len:2
Ref:const
Rows:1
Extra:using where

2. Row ***************************
Id:1
Select_type:simple
Table:c
Type:ref
possible_keys:index_company_id
key:index_company_id
Key_len:5
ref:sakila.c.company_id
Rows:1
Extra:using where; Using Index

MySQL Execution analysis