Bug #93339 EXPLAIN Join Types range description
Submitted: 26 Nov 2018 9:15 Modified: 27 Nov 2018 14:19
Reporter: donghongyu dong Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[26 Nov 2018 9:15] donghongyu dong
Description:
Hi, in the MySQL documentation The description of the range type in the section on EXPLAIN Join Types does not seem to be correct:
"range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

SELECT * FROM tbl_name
  WHERE key_column = 10;
"
"=" Query in the explain output result type should be ref 

How to repeat:
mysql> show create table geek;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geek  | CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from geek where c =10;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | geek  | NULL       | ref  | c             | c    | 4       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
[26 Nov 2018 13:17] Sinisa Milivojevic
HI,

Thank you for your documentation bug report.

However, I do not think that anything is missing from our Reference Manual. Let me explain ...

If the condition in the WHERE clause would have covered several rows, you would have seen `ref` in the EXPLAIN output. However, since during optimising state, only one row is  found, then the entire table in your example is replaced with one constant, covering all values in that filtered row.

I do think that this is fully covered in our Reference Manual for 5.7, as well as for 8.0, but if I am wrong, please let me know.
[27 Nov 2018 13:22] donghongyu dong
But the description in the official documentation says that the equivalent query is also a Range type.
[27 Nov 2018 13:26] donghongyu dong
If it is the equivalent query execution plan output type column should be `ref`,will not be `Range`
[27 Nov 2018 14:19] Sinisa Milivojevic
Hi,

What you are writing about is definitely correct. However, you have missed that part of our Reference Manual, a chapter on the Oprimiser, which states loud and clear what happens if number of found rows is just one.

Not a bug.