Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Description
Having the following query causes the database to make a table scan:
SELECT *,(SELECT GROUP_CONCAT(Name) FROM user WHERE ID IN (a,b,c)) FROM `test` WHERE a!=0 |
Explain returns:
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | PRIMARY | test | ALL | a | NULL | NULL | NULL | 1335 | Using where |
|
| 2 | DEPENDENT SUBQUERY | user | ALL | NULL | NULL | NULL | NULL | 4545 | Using where |
|
+------+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|
Table definitions:
CREATE TABLE `test` ( |
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`a` int(10) unsigned NOT NULL, |
`b` int(10) unsigned NOT NULL, |
`c` int(10) unsigned NOT NULL, |
PRIMARY KEY (`ID`), |
KEY `a` (`a`) |
) ENGINE=Aria
|
CREATE TABLE `user` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`name` varchar(10) COLLATE latin1_german1_ci NOT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=Aria
|
As you might guess the original expression is more complex. As you can see, the result of the subquery can only have 3 entries which is grouped. The Subquery is dependend so this has to be performed on each resulting row - correct. But why does the subquery don't use the primary index which would only use 3 result-entries, not 4545