This column is very important, showing which category is used and whether there are indexes. Type represents the connection method of the table specified in the inquiry execution plan (QEP). The worst connection type from best to the worst connection is 1.System, 2.Const, 3. EQ_reg, 4. Ref, 5. Range, 6.INDEX, 7. ALL

1). system

System is a special case, that is, there is only one record in the table.

2). const

CONST is a query condition in the where conditions, and there is a record match in the table. Because it is constant, you only need to read it once.

3). eq_ref

will only have one matching result at most, generally access by the main key or unique index. Generally appears in the sentences of connection query.

4）. ref

Non -unique sexual index scan, return to all the lines that match a single value. Common search for non -unique prefixes with non -unique indexes and unique indexes

5）.range

Index scanning, common in query of Between, <,>, in, etc.

6）. index

Full index tree scannedFull Index Scan

7). all

full table scan, the effect is the least ideal.

5. possible_keys

Query can be used to use the index. If there is no index to use it, it will be displayed as NULL. The adjustment of the index when optimizing the content is very important.

6.key

The index selected from Possible_keys

7. key_len

Key_len column displaymysqlThe key length of the use, if the key is null, the length is null. The length of the index used, the shorter the better.

8. ref 

represents the connection matching conditions of the above table, that is, which columns or constants are used to find the values on the index column

In this example, the IDX_COL1_COL2 of the T1 table can be fully used by the key_len. Col1 matches the color of the T2 table, and color2 matches a constant, that is, ‘AC’

9.rows

systemThe statistical information collected, the number of recorded records of the result set

10. extra

EXTRA: The additional detail information implemented in the query may be the following content:

1). DISTINCT: Find the distinct value, so when mysql finds the result of the first match, the query of the value is turned into the query of other values;

The optimization method in the

Inquiry is mainly to use the use of the NULL values that cannot be accessed by indexing;

2). ImpossibleWhereneoticeDaFTERREADINGCONSTTABLEs: MySQLQLQURYOMIZER determines the impossible results through the statistical information collected;

3). Notables: Use fromdual in the query statement or does not include any from classes;

4). Notexist: Optimized method used by MySQLQLQLQLQURYOPTIMIZER in the left connection, which can partially reduce the number of data access by changing the composition of the original Query;

5). RangecheckedForeachrecord (IndexMap: N): Until the description of the official HSQL manual, when mysqlqueryoptimizer not found a good index that can be used, if

It is found that if the column value of the previous table is known, some indexes may be used. For each line combination of the previous table, mysql checks whether the access method can be used to use the Range or index_merge access method

line.

6). SelecttableSoptimized Away: When we use some polymer functions to access a certain field of indexing, mysqlqueryoptimizer will directly position it at one time at one time indexing.

The data line required to complete the entire query. Of course, the premise is that GroupBY operations cannot be operated in Query. Such as using min () or max ();

7). USINGFILESORT: When our Query contains Orderby operation and cannot use indexes to complete the sorting operation, mysqlqueryOptimizer has to choose the corresponding sorting algorithm

to implement.

8). USingIndex: The required data only needs to be obtained in the index without having to get the data in the table;

9). USingIndexForgroup-By: The data access is the same as usingIndex. The required data only needs to read the index, and when the groupby or distinct sentence is used in Query,

If the group field is also in the index, the information in EXTRA will be USINGINDEXFORGROUP-BY;

10). Usingtemporary: When mysql must use a temporary table in certain operations, USINGTEMPORARY will appear in Extra information. Mainly common in Groupby and Orderby

Operation.

11). Useingwhere: If we are not read all the data of the table, or you can obtain all the data you need only through the index, the USINGWhere information will appear;

12). USingwhereWithPushedCondition: This is a message that only appears in the NDBCluster storage engine, and it also needs to open the ConnectionPushdown optimization function

can be used. The control parameter is Engine_Condition_Pushdown.

 

CREATE TABLE `item` (
  `i_id` int(11) NOT NULL,
  `i_im_id` int(11) DEFAULT NULL,
  `i_name` varchar(24) DEFAULT NULL,
  `i_price` decimal(5,2) DEFAULT NULL,
  `i_data` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
 
CREATE TABLE `orders` (
  `o_id` int(11) NOT NULL,
  `o_d_id` tinyint(4) NOT NULL,
  `o_w_id` smallint(6) NOT NULL,
  `o_c_id` int(11) DEFAULT NULL,
  `o_entry_d` datetime DEFAULT NULL,
  `o_carrier_id` tinyint(4) DEFAULT NULL,
  `o_ol_cnt` tinyint(4) DEFAULT NULL,
  `o_all_local` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`),
  KEY `idx_orders` (`o_w_id`,`o_d_id`,`o_c_id`,`o_id`),
  CONSTRAINT `fkey_orders_1` FOREIGN KEY (`o_w_id`, `o_d_id`, `o_c_id`) REFERENCES `customer` (`c_w_id`, `c_d_id`, `c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;