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

[30 Aug 2007 19:36] Tobias Asplund
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
[31 Aug 2007 11:01] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Nov 2007 8:29] terry tao
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)
[22 Sep 2008 14:48] Tobias Asplund
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.
[16 Jun 2019 5:09] Øystein Grøvlen
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)
[16 Jun 2019 5:10] Øystein Grøvlen
Version tested was MySQL 8.0.16, by the way.
[17 Jun 2019 11:16] Norvald Ryeng
Posted by developer:
 
Exists in 5.5 and older. Fixed in 5.6 (I haven't checked exactly which 5.6 version) and newer.