MySQL Query Analyzer explain or desc

MySQL can view and analyze the execution of SQL statements through explain or DESC, as follows to calculate sales for all companies in 2006, to correlate sales tables and company tables, and to sum the money fields with the corresponding SQL:

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

1. Row ***************************
Id:1 www.2cto.com
Select_type:simple
Table:s
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:1000
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 www.2cto.com
Extra:using where; Using Index

Description of the column:
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
The second or subsequent query statement in the Union: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, the difference is that after in is a subquery that queries a non-unique indexed field www.2cto.com
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

Through the analysis of explain, it is confirmed that in the above example, the full table scan of the sales table results in inefficient, by creating an index on the sales table: www.2cto.com
SQL code
CREATE INDEX index_sales_year on sales (year);

After the index is created, the query statement is parsed as follows:
SQL code
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 www.2cto.com
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 Query Analyzer explain or desc