MySQL Bugs: #72416: Index doesn‘t work when querying a view created as union of two table

Bug #72416	Index doesn‘t work when querying a view created as union of two table
Submitted:	22 Apr 2014 3:42	Modified:	21 Mar 2018 13:35
Reporter:	haili zou	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	index view

Description:
I face a problem recently. The index doesn‘t work when querying a view created as union of two table：

create table t1(v1 int, v2 int,  primary key (v1), key idx_t1_v2(v2), v3 int)
create table t2(v1 int, v2 int,  primary key (v1), key idx_t2_v2(v2), v3 int) 

mysql> explain select * from  (select * from t1 union all select * from t2)  a where a.v2=138000123;
+----+--------------+------------+------+---------------+-------------+---------+-------+-------+-----------------+
| id | select_type  | table      | type | possible_keys | key         | key_len | ref   | rows  | Extra           |
+----+--------------+------------+------+---------------+-------------+---------+-------+-------+-----------------+
|  1 | PRIMARY      | <derived2> | ref  | <auto_key0>   | <auto_key0> | 5       | const |    10 | NULL            |
|  2 | DERIVED      | t1         | ALL  | NULL          | NULL        | NULL    | NULL  | 57322 | NULL            |
|  3 | UNION        | t2         | ALL  | NULL          | NULL        | NULL    | NULL  | 52475 | NULL            |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL        | NULL    | NULL  |  NULL | Using temporary |
+----+--------------+------------+------+---------------+-------------+---------+-------+-------+-----------------+
4 rows in set (0.00 sec)

mysql> explain select * from t1 where v2=138000123 union all select * from t2 where v2=138000123;
+----+--------------+------------+------+---------------+-----------+---------+-------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key       | key_len | ref   | rows | Extra           |
+----+--------------+------------+------+---------------+-----------+---------+-------+------+-----------------+
|  1 | PRIMARY      | t1         | ref  | idx_t1_v2     | idx_t1_v2 | 5       | const |    1 | NULL            |
|  2 | UNION        | t2         | ref  | idx_t2_v2     | idx_t2_v2 | 5       | const |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL      | NULL    | NULL  | NULL | Using temporary |
+----+--------------+------------+------+---------------+-----------+---------+-------+------+-----------------+
3 rows in set (0.00 sec)

The question is why the index idx_t1_v2 and idx_t2_v2 dont work for the first statement, and it is work for second statement.

As I know, Both these two statements can use index. 

How to repeat:
see above

Hi!

No index will be used on empty tables and we got only empty tables from you, not their contents.

Much more important then this cause is that the first query is dependent nested query, and with such queries you can not use any index. This is partially explained in our Reference Manual and fully explained in many SQL textbooks.

If it is impossible, why do I see PostgreSQL actually use indexes then?

test=# create table t1(v1 int, v2 int,  primary key (v1), v3 int);
CREATE TABLE

test=# create index t1_v2 on t1 (v2);
CREATE INDEX

test=# create table t2(v1 int, v2 int,  primary key (v1), v3 int);
CREATE TABLE

test=# create index t2_v2 on t2 (v2);
CREATE INDEX

test=# insert into t1 values(1, 1, 1);
INSERT 0 1

test=# insert into t1 values(2, 2, 2);
INSERT 0 1

test=# insert into t1 values(3, 3, 3);
INSERT 0 1

test=# insert into t1 values(4, 4, 4);
INSERT 0 1

test=# insert into t1 values(5, 5, 5);
INSERT 0 1

test=# insert into t2 select * from t1;
INSERT 0 5

test=# explain select * from  (select * from t1 union all select * from t2)  a where a.v2 = 4;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Append  (cost=4.23..29.57 rows=20 width=12)
   ->  Bitmap Heap Scan on t1  (cost=4.23..14.79 rows=10 width=12)
         Recheck Cond: (v2 = 4)
         ->  Bitmap Index Scan on t1_v2  (cost=0.00..4.23 rows=10 width=0)
               Index Cond: (v2 = 4)
   ->  Bitmap Heap Scan on t2  (cost=4.23..14.79 rows=10 width=12)
         Recheck Cond: (v2 = 4)
         ->  Bitmap Index Scan on t2_v2  (cost=0.00..4.23 rows=10 width=0)
               Index Cond: (v2 = 4)
(9 rows)

Hi Hartmut,

No, it is not impossible. Standard prescribes that these nested queries are executed for each row. If I am not wrong, you have been around when decision was made to follow the textbooks.

This issue is circumvented by several products, but not by all.

There are internal plans on when this will be optimised in our server. There are already a good number of feature requests on the same issue, but adding another feature request will not make any changes in our internal plans. These plans will stay internal only and will not be divulged.