Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.36, 10.0.10
-
None
Description
Create a test dataset:
create table t1 (a int);
|
insert into t1 select * from test.one_k;
|
|
|
create table tsubq(
|
id int primary key,
|
key1 int,
|
col1 int,
|
key(key1)
|
) engine=innodb;
|
|
|
insert into tsubq
|
select A.a + B.a*1000, A.a, 123456 from test.one_k A, test.one_k B;
|
Then, check the plan:
explain select
|
(SELECT
|
concat(id, '-', key1, '-', col1)
|
FROM tsubq
|
WHERE tsubq.key1 = t1.a
|
ORDER BY tsubq.id ASC LIMIT 1)
|
from
|
t1;
|
+------+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | |
|
| 2 | DEPENDENT SUBQUERY | tsubq | index | key1 | PRIMARY | 4 | NULL | 1 | Using where |
|
+------+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
|
The subquery uses "index" access, which is very inefficient. The estimate for #rows seems to come from the LIMIT clause and is very wrong in this case.
The table is InnoDB (with extended keys). The index KEY(key1) is actually KEY(key1, id). The query has a restriction on key1 which makes it constant (tsubq.key1 = t1.a). After that, ORDER BY tsubq.id is achieved automatically.
The problem seems to be specifically with references to outside of subquery. If I use a constant instead, the query plan is able to use key1:
explain select (SELECT concat(id, '-', key1, '-', col1) FROM tsubq WHERE tsubq.key1 = 333 ORDER BY tsubq.id ASC LIMIT 1) from t1;
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | |
|
| 2 | SUBQUERY | tsubq | ref | key1 | key1 | 5 | const | 999 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-