Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use EXPLAIN commands in MySQL", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use EXPLAIN commands in MySQL" this article.

Explain shows how mysql handles select statements and join tables.

Here is an example of the explain command:

C:\ Users\ duansf > mysql-u root-p

Enter password: *

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.5.13 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | workinfo |

+-+

1 row in set (0.00 sec)

Mysql > explain select * from workinfo\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: workinfo

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 1

Extra:

1 row in set (0.00 sec)

Mysql >

1 、 id

SELECT identifier, which is the SELECT query serial number. This is not important. The query sequence number is the order in which the sql statement is executed.

2 、 select_type

The select type, which has the following values:

Simple: it represents a simple select, without union and subqueries

Primary: outermost select. In statements with subqueries, the outermost select query is primary.

The second and subsequent subqueries of the union:union statement

The second and subsequent subquery statements in dependent union:UNION, depending on the external query

Results of union result:UNION

Subquery: the first select statement in a subquery

Dependent subquery: the first select in a subquery that depends on an external query

Derived: a table derived from a subquery

Materialized: materialized subquery

Uncacheable subquery: subqueries that cannot be cached and need to be reevaluated

Subqueries that need to be reevaluated in the second or more uncacheable subquery in uncacheable union:union

3 、 table

The table used for the output row

4 、 type

Connection Typ

System: the table has only one row. This is a special column of const type, which usually does not appear, and this can be ignored.

Const: the table has at most one matching row, and const is used to compare primary key or unique indexes. Because it matches only one row of data, it's fast. Remember that if primary key or unique is used and only two pieces of data are retrieved, it will be const

Eq_ref: for each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for the const type. It is used by joins in all parts of an index and the index is UNIQUE or PRIMARY KEY. Eq_ref can be used to compare indexed columns.

Ref: for each row combination from the previous table, all rows with matching index values will be read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the keyword). This join type is good if the keys used match only a small number of rows.

Fulltext: using full-text indexing

Ref_or_null: this join type is like ref, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

Index_merge: this join type indicates that the index merge optimization method is used. In this case, the key column contains a list of the indexes used, and the key_len contains the longest key elements of the index used.

Unique_subquery: used instead of ref in clauses with in

Index_subquery: similar to unique_subquery, query condition

Range: search within a given range, using an index to check rows, using =, >, =,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.