| 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: | |
| 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: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.
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