Bug #36802 MySQL only use covered index when selecting from a view (or subquery) with union
Submitted: 19 May 2008 15:42 Modified: 5 Jan 2022 15:50
Reporter: Alexander Rubin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0, 5.1, 6.0 BK OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: INDEX, Optimizer, UNION, VIEW

[19 May 2008 15:42] Alexander Rubin
Description:
MySQL only use covered index when selecting from a view (or subquery) with union and can't not use other indexes if no covered index is present.
Here is the test case:

mysql> /* Create two identical tables */
mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, val1 INT, val2 INT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, val1 INT, val2 INT, PRIMARY KEY (id)) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> /* Create index on val1 column */
mysql> CREATE INDEX I_T1_VAL1 on t1(val1);
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX I_T2_VAL1 on t2(val1);
Query OK, 0 rows affected (0.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> /* Create a view with these two tables */
mysql> CREATE OR replace VIEW v_t AS
    -> SELECT id, val1 FROM t1
    -> UNION ALL
    -> SELECT id, val1 FROM t2;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> /* insert some values */
mysql> insert into t1 (val1) values (1), (2), (12), (12), (13) ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t2 (val1) values  (1), (2), (12), (12), (13);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> /* This request use indexes correctly */
mysql> EXPLAIN SELECT * FROM v_t WHERE val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: I_T1_VAL1
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: index
possible_keys: NULL
          key: I_T2_VAL1
      key_len: 5
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.00 sec)

/* this will not use indexes */

mysql> CREATE OR replace ALGORITHM = MERGE  VIEW  v_t AS
    -> SELECT id, val1, val2 FROM t1
    -> UNION ALL
    -> SELECT id, val1, val2 FROM t2 ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> explain select * from v_t where val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra:
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra:
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.01 sec)

If we add index on (val1, val2) MySQL will use index:
mysql> alter table t1 add key (val1, val2);
Query OK, 5 rows affected (0.25 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> alter table t2 add key (val1, val2);
Query OK, 5 rows affected (0.27 sec)
Records: 5  Duplicates: 0  Warnings: 0

Now our query will use indexes:

mysql> CREATE OR replace VIEW v_t AS
    -> SELECT id, val1, val2 FROM t1 FORCE INDEX (I_T1_VAL1)
    -> UNION ALL
    -> SELECT id, val1, val2 FROM t2 FORCE INDEX (I_T2_VAL1); Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM v_t WHERE val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: val1
      key_len: 10
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: index
possible_keys: NULL
          key: val1
      key_len: 10
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.00 sec)

For MyISAM will need to add true covered index with all fields.

mysql> alter table t1 engine=MyISAM; alter table t2 engine=MyISAM;
Query OK, 5 rows affected (0.22 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> alter table t1 add key (id, val1, val2);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> alter table t2 add key (id, val1, val2);
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

/* this will use index */

mysql> EXPLAIN SELECT * FROM v_t WHERE val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: id_2
      key_len: 14
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: index
possible_keys: NULL
          key: id_2
      key_len: 14
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.02 sec)

As we can see, MySQL will use index only if we have "covered" index and is unable to use non-covered index with view or subquery with union

How to repeat:
see above

Suggested fix:
Fix optimizer to use indexes without covered index
[19 May 2008 15:43] Alexander Rubin
Same is with subquery in the from clause:

1. No covered index:
mysql> explain
    -> select * from
    -> (SELECT id, val1, val2 FROM t1 UNION ALL SELECT id, val1, val2 FROM t2  ) as t
    -> where val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra:
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.00 sec)

2. With covered index:
mysql> explain
    -> select * from
    -> (SELECT id, val1, val2 FROM t1 UNION ALL SELECT id, val1, val2 FROM t2  ) as t
    -> where val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: val1
      key_len: 10
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: index
possible_keys: NULL
          key: val1
      key_len: 10
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.01 sec)
[19 May 2008 16:22] Sveta Smirnova
Thank you for the report.

Verified as described.

With MyISAM storage engine indexes are not used in any case.
[19 May 2008 16:34] Alexander Rubin
Sveta, with MyISAM index on (id, val1), covered, is used:

mysql> /* Create two identical tables */
mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, val1 INT, val2 INT, PRIMARY KEY (id)) engine=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT, val1 INT, val2 INT, PRIMARY KEY (id)) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> /* Create indexes */
mysql> CREATE INDEX cov1 on t1(id, val1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX cov1 on t2(id, val1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> /* insert some values */
mysql> insert into t1 (val1) values (1), (2), (12), (12), (13) ;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t2 (val1) values  (1), (2), (12), (12), (13);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> /* Create a view with these two tables */
mysql> CREATE OR replace VIEW v_t AS
    -> SELECT id, val1 FROM t1
    -> UNION ALL
    -> SELECT id, val1 FROM t2;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> /* This request use indexes correctly */
mysql> EXPLAIN SELECT * FROM v_t WHERE val1=12\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
         type: index
possible_keys: NULL
          key: cov1
      key_len: 9
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: t2
         type: index
possible_keys: NULL
          key: cov1
      key_len: 9
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 4. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union2,3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
4 rows in set (0.00 sec)
[23 Sep 2008 7:00] Sveta Smirnova
Gleb, I agree.
[9 Jun 2010 14:15] Manyi Lu
Evgeny in email exchange with Manyi wrote:
The feature requested in the bug pushes down only parts of conditions from outer query into subqueries/views (any materialized subquery/view not just those with unions). It requires wl#5274. After this wl is pushed it would be relatively easy to fix this bug.
[23 May 2012 22:12] Onur Keskin
Are there any plans for this to be implemented? 
Thanks
[5 Jan 2022 15:50] Jon Stephens
Fixed in MySQL 8.0.29 as part of work done for WL#13730. See same for more info.

Closed.