MySQL manual version 5.0.20-mysql Optimization

Http://tech.ccidnet.com/art/321/20060817/832025_3.html

 

 

The connection type is the same as that of all. The difference is that it only scans the index tree. It is usually faster than all because the index file is usually smaller than the data file. MySQL uses this connection type when the queried field knowledge is a separate part of the index.

 

All

 

Scan all the tables and combine the records obtained from the previous table. At this time, if the first table is not identified as const, it is not very good. In other cases, it is usually very bad. Normally, you can add indexes to quickly retrieve records from the table to avoid all.

 

Possible_keys

 

The possible_keys field indicates the index that MySQL may use when searching table records. Note that this field is completely independent from the table sequence displayed in the explain statement. This means that the indexes contained in possible_keys may not be used in actual use. If the value of this field is null, it indicates that no index is used. In this case, you can check which fields in the WHERE clause are suitable for adding indexes to improve query performance. In this case, create an index and then use the explain command to check the index. For details, see section "14.2.2 alter table Syntax ". If you want to see what indexes are available for the table, you can use show index from tbl_name.

 

Key

 

The key field shows the indexes actually used by MySQL. When no index is used, the value of this field is null. To enable MySQL to forcibly use or ignore the index list in the possible_keys field, you can use the keyword Force Index, use index, or ignore index in the query statement. For MyISAM and bdb tables, you can use analyze table to analyze which index is better. For tables of the MyISAM type, the same effect is achieved by running the command myisamchk -- analyze. For details, see chapter "14.5.2.1 analyze table Syntax" and "5.7.2 table maintenance and crash recovery ".

 

Key_len

 

The key_len field shows the index length used by MySQL. If the value of the key field is null, the index length is null. Note that the value of key_len tells you which indexes MySQL uses in the Union index.

 

Ref

 

The ref field shows which fields or constants are used to work with keys to query records from the table.

 

Rows

 

The Rows field shows the number of records that MySQL considers to be retrieved in the query.

 

Extra

 

This field displays the additional MySQL information in the query. The following is an explanation of several different values of this field:

 

Distinct

 

When MySQL finds the first record of the matching Union result of the current record, it no longer searches for other records.

 

Not exists

 

When MySQL performs a left join optimization during query, when it finds that the current table matches the left join condition with the previous record, it will no longer search for more records. The following is an example of this type of query:

 

Select * from T1 left join T2 on t1.id = t2.id

Where t2.id is null;

If t2.id is defined as not null. In this case, MySQL scans table T1 and searches for records in T2. When a matched record is found in t2. This means that t2.id will not be null, and other records with the same ID value will not be searched in T2. It can also be said that for each record in T1, MySQL only needs to perform a search in T2. no matter how many matching records actually exist in T2.

 

Range checked for each record (index map :#)

 

MySQL does not find an appropriate available index. Instead, for each row join in the previous table, it performs a test to determine which index to use (if any ), use this index to retrieve records from the table. This process is not very fast, but it is always faster than doing table join joins without any indexes.

 

Using filesort

 

MySQL needs to perform an additional step to obtain records in an ordered order. SortProgramTraverse all records based on the connection type, and store the keys to be sorted and pointers to records that meet the where condition. These keys have been sorted out, and the corresponding records are also sorted out. For details, see "7.2.9 how MySQL optimizes order ".

 

Using Index

 

The field information is obtained directly from the index tree, instead of scanning the actual records. Fields used for query are part of an independent index.

 

Using Temporary

 

MySQL needs to create a temporary table Storage result to complete the query. This usually occurs when the query contains the group by and order by clauses, which list fields in different ways.

 

Using where

 

The where clause will be used to limit which records match the next table or send to the client. Unless you particularly want to obtain or check all records, it may indicate a problem when the queried extra field value is not using where and the table connection type is all or index.

 

If you want to make the query as fast as possible, you should note that the extra field values are using filesort and using temporary.

 

you can get a rough idea of how the connection works by the product of the rows field value in the explain result. It roughly tells us how many records MySQL will query during the Query Process. If the system variable max_join_size is used to obtain the query result, the product can also be used to determine which multi-Table select statements will be executed. For details, see "7.5.2 tuning server Parameters ".