Mysql explain usage (use explain to optimize the query statement), mysqlexplain

Mysql explain usage (use explain to optimize the query statement), mysqlexplain

First, I will give a simple example and then explain the information in the explain column.

Table 1: catefory document category table:

Create table if not exists 'category '('id' smallint (5) unsigned not null AUTO_INCREMENT, 'name' varchar (50) not null default '', primary key ('id') ENGINE = MyISAM insert into 'test '. 'category 'values (NULL, 'category 1'); insert into 'test '. 'category 'values (NULL, 'category 2'); insert into 'test '. 'category 'values (NULL, 'category 3 ');

Table 2: article table:

Create table if not exists 'Article' ('aid 'int (11) not null, 'cid' int (11) not null, 'content' text not null, primary key ('aid '), KEY 'cid' ('cid') ENGINE = MyISAMINSERT INTO 'test '. 'Article' ('aid ', 'cid', 'content') VALUES ('', '7', ' (jb51.net) ');

Execute explain:

EXPLAIN SELECT name, contentFROM category, articleWHERE category.id = article.cid

Expected result:

Description of the EXPLAIN column:

Id: The serial number queried in the selected execution plan. Indicates the order in which the select clause or operation table is executed in the query. The higher the id value, the higher the priority. The execution sequence is from top to bottom.

Select_type: Query type description

1. SIMPLE: SIMPLE select queries, without union and subqueries
2. PRIMARY: select query on the outermost layer
3. UNION: the second or subsequent select query in UNION does not depend on the result set of the external query.
4. dependent union: the second or subsequent select query in UNION depends on the result set of the external query.
5. union result: the first select query in the SUBQUERY of the UNION query RESULT set does not depend on the RESULT set of the external query.
6. dependent subquery: The first select query in the SUBQUERY depends on the result set DERIVED of the external query for subqueries in the from clause.
MySQL recursively executes these subqueries and places the results in the temporary table.
7. uncacheable subquery: subqueries whose result set cannot be cached must be re-evaluated for each row in the outer query.
8. uncacheable union: the second or subsequent select queries in UNION are non-cacheable subqueries.

Table: displays the data of this row about which table
Type: this is an important column that shows the type used by the connection. The connection types from the best to the worst are const, eq_reg, ref, range, index, and ALL.
All: full table scan; mysql will traverse the entire table to find matched rows;
Index: index scan; the difference between index and all is that the index type only traverses the index;
Range: index range scanning. The index scanning starts at a certain point. Rows with matching values are returned. Common queries include between, <,>;
Ref: Non-unique index scan. It returns all rows that match a single value. It is commonly used to search for non-unique prefixes of a non-unique index;
Eq_ref: unique index scan. For each index key, only one record matches in the table, which is often used for primary key or unique index scanning;
Const, system: when mysql optimizes a certain part of a query and converts it to a constant, these access types are used. If the primary key is placed in the where list, mysql can convert the query into a constant.
Possible_keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain.
Key: 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: indicates which column of the index is used. If possible, it is a constant.
Rows: the number of rows that MYSQL deems necessary to check to return the requested data
Extra: Extra 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, and the result is that the retrieval will be slow.
Because the real optimization will take into account big data, I will write a more detailed optimization tutorial later. I am tired today! Share a detailed mysql explainsyntax and usage tutorial (mysql_explain_syntax )!