MySQL manual version 5.0.20-mysql optimization (ii)

mysql| optimization

7.2.1 EXPLAIN syntax (get relevant information about Select)

EXPLAIN Tbl_name

Or:

EXPLAIN SELECT select_options

The EXPLAIN statement can be used as a synonym for DESCRIBE, or it can be used to get information about a SELECT statement that MySQL is going to execute.

EXPLAIN tbl_name syntax is the same as DESCRIBE tbl_name or show COLUMNS from Tbl_name.

When the keyword EXPLAIN is used before a SELECT statement, MySQL explains how the SELECT statement is about to run, showing how the table is connected, the order of the connections, and so on.

This chapter focuses on the second EXPLAIN usage.

With the help of EXPLAIN, you know when to add an index to a table to use the index to find records so that the SELECT runs faster.

If some problems arise from improper use of the index, you can run ANALYZE table to update the table's statistics, such as the cardinality of the key, to help you make better choices about optimization. Please see "14.5.2.1 ANALYZE TABLE Syntax" for details.

You can also see whether the optimizer connects the datasheet in the best order. In order for the optimizer to connect in the order of the table names in the SELECT statement, you can use select Straight_join instead of select only at the beginning of the query.

EXPLAIN returns a row of records that includes information about the tables used in the SELECT statement. These tables are listed in the results in the order in which they are read in the query that MySQL is about to execute. MySQL solves the connection by scanning multiple connections (single-sweep, Multi-join) one at a time. This means that MySQL reads a record from the first table, then finds the corresponding record in the second table, and then looks in the third table, and so on. When all the tables are scanned, it outputs the selected fields and backtracking all the tables until it is not found, because there may be more than one matching record in some of the tables. The next record will be read from the table and continue processing from the next table.

In MySQL version 4.1, the result format of the EXPLAIN output changes, making it more appropriate for example UNION statements, subqueries, and the structure of derived tables. More notably, it adds 2 fields: IDs and Select_type. These fields are not visible when you use a version earlier than MySQL 4.1.

Each row of the EXPLAIN results shows information about each table, and each row contains the following fields:

Id

The identifier for this SELECT. In a query, each SELECT has a numeric value in the order.

Select_type

The type of SELECT may have the following:

Simple

Simple SELECT (no UNION or subquery used)

PRIMARY

The outermost SELECT.

UNION

The second layer, which uses UNION after the select.

DEPENDENT UNION

The second SELECT in the UNION statement relies on the outer subquery

Subquery

The first SELECT in a subquery

DEPENDENT subquery

The first subquery in a subquery relies on an external subquery

DERIVED

Derived table SELECT (subquery in FROM clause)

Table

Records the tables referenced by the query.

Type

Table connection type. Here are a list of different types of table joins, from best to worst in turn:

System

The table has only one row of records (equal to the system table). This is a special case of the const table join type.

Const

The table has at most one row matching records that are read at the beginning of the query. Because there is only one row of records, the field values of the row records in the remainder of the optimizer can be treated as a constant value. The Const table query is very fast because just read it once! Const is used in situations where there is a fixed value comparison with a PRIMARY KEY or a UNIQUE index. In the following few queries, the tbl_name is the const table:

SELECT * from Tbl_name WHERE primary_key=1;

SELECT * from Tbl_name

WHERE Primary_key_part1=1 and primary_key_part2=2;

Eq_ref

A row of records from the table is read to unite with records read from the previous table. Unlike the const type, this is the best connection type. It is used in all parts of the index to make a connection and this index is a PRIMARY KEY or a UNIQUE type. Eq_ref can be used to retrieve a field when a "=" comparison is made. The value of the comparison can be either a fixed value or an expression in which the fields in the table can be used, and they are ready before they are read. In the following examples, MySQL uses a eq_ref connection to handle ref_table:

SELECT * from Ref_table,other_table

WHERE Ref_table.key_column=other_table.column;

SELECT * from Ref_table,other_table

WHERE Ref_table.key_column_part1=other_table.column

and ref_table.key_column_part2=1;

Ref

All records that match the retrieved values in the table are pulled out and combined with records taken out of the previous table. Ref is used by the connector to use the leftmost prefix of a key, or if the key is not a PRIMARY key or a UNIQUE index (in other words, the connection program cannot obtain only one record based on the key value). This is a good connection type when only a few matching records are queried based on the key value. Ref can also be used to retrieve the time when the field is compared using the = operator. In the following few examples, MySQL will use ref to process ref_table:

SELECT * from ref_table WHERE key_column=expr;

SELECT * from Ref_table,other_table

WHERE Ref_table.key_column=other_table.column;

SELECT * from Ref_table,other_table

WHERE Ref_table.key_column_part1=other_table.column

and ref_table.key_column_part2=1;

Ref_or_null

This type of connection is similar to ref, and MySQL will search for additional records that contain NULL values at the time of retrieval. This type of connection is optimized from the MySQL 4.1.1, which is often used for subqueries. In the following example, MySQL uses the ref_or_null type to handle ref_table:

SELECT * from Ref_table

WHERE key_column=expr OR Key_column is NULL;

For more information, please see "7.2.6 How to MySQL optimizes is NULL".

Index_merge

This type of connection means that the Index Merge optimization method is used. In this case, the key field includes all the indexes used, and Key_len includes the longest portion of the keys used. Please see "7.2.5 how MySQL optimizes OR clauses" for details.

Unique_subquery

This type replaces ref with, for example, a form in subquery:

Value in (SELECT primary_key from single_table WHERE some_expr)

Unique_subquery is more efficient than the index lookup function that is used to completely replace a subquery.

Index_subquery

This type of connection is similar to Unique_subquery. It replaces in with a subquery, but it is used in cases where there is no unique index in the subquery, such as the following:

Value in (SELECT key_column from single_table WHERE some_expr)

Range

Only records in a given range are taken out and the index is used to get a record. The key field indicates which index is used. The Key_len field includes the longest part of the key used. The REF field value is NULL when this type. Range is used to compare a field and a colonization with any of the following operators =, <>, >=,,, <=, is NULL, <=>, BETWEEN, or in:

SELECT * from Tbl_name

WHERE key_column = 10;

SELECT * from Tbl_name

WHERE Key_column BETWEEN and 20;

SELECT * from Tbl_name

WHERE Key_column in (10,20,30);

SELECT * from Tbl_name

WHERE key_part1= and Key_part2 in (10,20,30);

Index