MySQL Manual Version 5.0.20-MySQL Optimization (2) (1) (3)_PHP Tutorial

The connection type is the same as ALL, except that it only scans the index tree. It is usually faster than ALL because index files are usually smaller than data files. MySQL uses this join type in situations where the queried field knowledge is part of a separate index.

ALL

will do a full scan of the table to join with the records obtained from the previous table. At this point it would be bad if the first table was not marked as const, which is usually very bad in other situations. Normally, ALL can be avoided by adding indexes to retrieve records from the table faster.

possible_keys

The possible_keys field refers to which index MySQL may use when searching for table records. Note that this field is completely independent of the table order displayed by EXPLAIN. 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 means 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. That's it, create the index and then check it with EXPLAIN. See chapter "14.2.2 ALTER TABLE Syntax" for details. If you want to see what indexes the table has, you can run SHOW INDEX FROM tbl_name.

key

key field shows the index that MySQL actually uses. When no index is used, the value of this field is NULL. To force MySQL to use or ignore the index list in the possible_keys field, use the keywords FORCE INDEX, USE INDEX, or IGNORE INDEX in the query statement. For MyISAM and BDB type tables, you can use ANALYZE TABLE to help analyze which index is better to use. If it is a MyISAM type table, running the command myisamchk --analyze will have the same effect. For details, please see chapters "14.5.2.1 ANALYZE TABLE Syntax" and "5.7.2 Table Maintenance and Crash Recovery".

key_len

key_len field shows the length of the index used by MySQL. When the value of the key field is NULL, the length of the index is NULL. Note that the key_len value tells you which indexes MySQL will actually use in the joint index.