The difference between using index and using where using index in the MySQL execution plan extra is as follows:

The difference between using index and using where using index in the MySQL execution plan extra is as follows:

 

Source: http://www.cnblogs.com/wy123/p/7366486.html
(The Source retained is not the right of original works. My work is far from reaching this level, just to link to the original article, because some possible errors will be corrected or supplemented later, without him)

 

The extra column in the mysql execution plan shows the implementation details in each step of the Execution Plan, including some index-related details.
The using index Related to the index may contain Using index, Using where Using index, and Using index condition in different cases.
So what is the difference between Using index and Using where; Using index? I searched for the article on the Internet, but I didn't understand it. So I tried it myself.

This article only tests using index and using where using index from the simplest single table, and briefly tests using index condition.
Execution Plan generation is related to multiple environments, such as the table structure, table data volume, index structure, statistical information, and so on. It cannot be generalized, And the complexity is also discussed.

 

Test Environment setup

Build the test table and test data, similar to the order table and order list. Currently, use the order table for testing.

Test Table Structure

create table test_order(    id int auto_increment primary key,    user_id int,    order_id int,    order_status tinyint,    create_date datetime);

create table test_orderdetail(    id int auto_increment primary key,    order_id int,    product_name varchar(100),    cnt int,    create_date datetime);create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);create index idx_orderid_productname on test_orderdetail(order_id,product_name);

Test data (50 W)

CREATE DEFINER=`root`@`%` PROCEDURE `test_insertdata`(IN `loopcount` INT)    LANGUAGE SQL    NOT DETERMINISTIC    CONTAINS SQL    SQL SECURITY DEFINER    COMMENT ''BEGIN    declare v_uuid  varchar(50);    while loopcount>0 do        set v_uuid = uuid();        insert into test_order (user_id,order_id,order_status,create_date) values (rand()*1000,id,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));        insert into test_orderdetail(order_id,product_name,cnt,create_date) values (rand()*100000,v_uuid,rand()*10,DATE_ADD(NOW(), INTERVAL - RAND()*20000 HOUR));        set loopcount = loopcount -1;    end while;END

 

Using index VS Using where Using index

First, in the "Order table", here is a multi-Column Composite Index
Create index idx_userid_order_id_createdate on test_order (user_id, order_id, create_date );

 

Using index

1. The queried column is overwritten by the index, and the where filtering condition is that the index is the leading column, and the Extra is the Using index.

 

Using where Using index

1. The queried column is overwritten by the index, and the where filtering condition is one of the index columns, but not the index and not the leading column. In Extra, the column is Using where; Using index, this means that you cannot directly query the Qualified Data through index search.

2. The queried column is covered by the index, and the where filter condition is a range of the leading column of the index column. It also means that the data that meets the query conditions cannot be directly searched through the index.

NULL (There is neither Using index nor Using where Using index, nor using where)

1. The queried column is not covered by the index, and the where filter condition is the leading column of the index,
This means that the index is used, but some fields are not covered by the index and must be implemented through "back-to-table". indexes are not used purely or completely, extra is NULL (no information)

 

Using where

1. The queried columns are not covered by the index. The where filter condition is a non-indexed leading column, and the Extra value is Using where.

2. The queried columns are not covered by the index. The where filter condition is not an indexed column. In Extra, the column is Using where.

Using where means that the process where condition is filtered by indexing or table scanning,
Conversely, there is no available index search. Of course, the cost of index scanning + back-to-table and table scanning should also be considered here.
All types here indicate that MySQL considers full table scan as a low cost.

Using index condition

　1, -- the queried columns are not all in the index, and the where condition is the range of a leading Column

　　

2. the query column is not completely covered by the index, and the query conditions can be fully indexed)

　　

Reference: MySQL · Feature Analysis · Index Condition Pushdown (ICP)

Using index condition occurs when multiple tables are associated. Currently, the internal implementation mode of Using index condition is not very well understood.

 

Conclusion:
1. Using index in Extra
The where Filter column is the index's leading column & the query column is covered by the index & the where filter condition is a query based on the index's leading column, this means that the data that meets the condition can be directly found through the index oversearch, and no need to return to the table

2. Extra is empty.
The existence of a query column that is not covered by the index & where filtering column is the leading column of the index, which means that fields that are not covered by the index are found through the index oversearch and "back to the table,

3. Using where Using index in Extra:
The emergence of Using where Using index means that SQL statements are executed through index scanning (or table scanning). Even the index range search of the index's leading column has a range scan, whether it is caused by a non-index leading column or a non-index column query.

 

Unsolved problems:

Query 1

Query 2

Query 3 (logically equivalent to query 1 + query 2), and the Execution Plan has undergone great changes.

 

Summary:

The Extra in the MySQL Execution Plan contains a lot of information, not only Using index, Using where Using index, Using index condition, and Using where, especially when connecting multiple tables, this is not intuitive or structured in MSSQL.
In MSSQL, index search, index scan, and table scan are used to perform specific queries, this graphical execution plan is very intuitive in different scenarios. To fully understand the execution plan of MySQL, you may need to explore it more in practice.