mysql sql plan ,what type=range meaning in this sql

Question

explain SELECT * FROM pop_order_info poi  WHERE poi.is_delete = 0  and poi.vendor_id =7879  group by poi.order_sn limit 10;        
+----+-------------+-------+-------+----------------------------------------------------+------------------------------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys                                      | key                          | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+----------------------------------------------------+------------------------------+---------+------+--------+-------------+

|  1 | SIMPLE      | poi   | range | vendor_id_2,is_delete_vendor_id_order_sn,vendor_id | is_delete_vendor_id_order_sn | 5       | NULL | 158462 | Using where |
+----+-------------+-------+-------+----------------------------------------------------+------------------------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

The above query is very slow! I created an index: is_delete_vendor_id_order_sn(is_delete,vendor_id,order_sn).

What does the 'type=range' mean in the above explain output?

Very fast by add hint:

explain SELECT * FROM pop_order_info poi use index(is_delete_vendor_id_order_sn) WHERE poi.is_delete = 0  and poi.vendor_id =7879  group by poi.order_sn limit 10;      
+----+-------------+-------+------+------------------------------+------------------------------+---------+-------------+--------+-------------+
| id | select_type | table | type | possible_keys                | key                          | key_len | ref         | rows   | Extra       |
+----+-------------+-------+------+------------------------------+------------------------------+---------+-------------+--------+-------------+

|  1 | SIMPLE      | poi   | ref  | is_delete_vendor_id_order_sn | is_delete_vendor_id_order_sn | 5       | const,const | 158462 | Using where |
+----+-------------+-------+------+------------------------------+------------------------------+---------+-------------+--------+-------------+
1 row in set (0.00 sec)

thanks for formating the code. – Yafang Gao Nov 25 '15 at 12:50 — Yafang Gao, Nov 25 '15 at 12:50
What version? I reproduced something similar in 5.6.12. – Rick James Nov 27 '15 at 23:05 — Rick James, Nov 27 '15 at 23:05

score 3 · Answer 1 · edited May 23 '17 at 12:15

The type column of EXPLAIN output describes how tables are joined.

The following list describes the join types, ordered from the best type to the worst:

system

const

eq_ref

ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN() operators:

If you are wonsering why join type , when retrieving from single table ,Read this

mysql sql plan ,what type=range meaning in this sql

2 Answers2