MySQL Bugs: #30733: Using where is shown in EXPLAIN output when there is no condition pushdown.

Bug #30733	Using where is shown in EXPLAIN output when there is no condition pushdown.
Submitted:	30 Aug 2007 19:36	Modified:	17 Jun 2019 11:16
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.20, 5.0.45, 4.1 BK	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Using where should not be shown when the whole WHERE condition is satisfied by an index lookup.

How to repeat:
CREATE TABLE t1 ( a INT, INDEX (a) ) ENGINE = MyISAM;
INSERT INTO t1 VALUES (1), (2), (3), (4);
EXPLAIN SELECT * FROM t1 WHERE a = 2 \G

Thank you for the report.

Verified as described.

when using index,if the mysql find the table is const,it will use the index only,otherwise the where condition is involved?

Verified as on version 5.1.23.
mysql> show variables like 'version';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| version       | 5.1.23-beta-debug-log |
+---------------+-----------------------+

mysql> drop tables t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 ( a INT,index (a) ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where a=2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.04 sec)

mysql> drop tables t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 ( a INT,unique index (a) ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.31 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where a=2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: const
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.03 sec)

mysql> CREATE TABLE t1 ( a INT,primary key (a) ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (1), (2), (3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 where a=2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.04 sec)

ERROR:
No query specified

mysql> explain select * from t1 where a=2 or a=3 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using where; Using index
1 row in set (0.00 sec)

It also seems to show this in error on a prefix-column search on a composite index:

mysql> show create table CountryLanguage \G
*************************** 1. row ***************************
       Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM CountryLanguage WHERE CountryCode = 'SWE' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CountryLanguage
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

Since the first key-part should be able to find all rows matching the WHERE criteria I would expect "Using where" not to show up here.

I think this bug can be closed.  These queries no longer show "Using where" in EXPLAIN:

mysql> EXPLAIN SELECT * FROM CountryLanguage WHERE CountryCode = 'SWE' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CountryLanguage
   partitions: NULL
         type: ref
possible_keys: PRIMARY,CountryCode
          key: PRIMARY
      key_len: 3
          ref: const
         rows: 6
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE a = 2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Version tested was MySQL 8.0.16, by the way.

Posted by developer:
 
Exists in 5.5 and older. Fixed in 5.6 (I haven't checked exactly which 5.6 version) and newer.