Unexplained very bad subquery performance
Hi,
I'm trying to optimize a database to have a response time <0.05s
However, I've to use a subquery and it has catastrophic performance.
I've already investigate and I have a very simple example that shows the problem.
CREATE TABLE `documents` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) NOT NULL DEFAULT '',
`ID_Project` int(11) DEFAULT NULL,
`misc_flags` bigint(20) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `ID_Project` (`ID_Project`),
KEY `misc_flags` (`misc_flags`)
) ENGINE=InnoDB;
populate with 100.000 rows randomly
Now the profiling:
+----------+------------+-----------------------------------------------------------------------------------
| Query_ID | Duration | Query
+----------+------------+-----------------------------------------------------------------------------------
| 1 | 0.00053450 | select * from documents where ID in (188)
| 2 | 0.85610375 | select * from documents where ID in (select ID from documents where ID in (19))
| 3 | 0.65760625 | select * from documents where ID in (select ID from documents where ID in (189))
+----------+------------+-----------------------------------------------------------------------------------
of course, the queries 2 and 3 are stupid and are just to illustrate the problem. They are 1700 times slower !!! and I don't understand why.
This example shows simply that when the "list" of IDs is a constant it is "instantly" and when the list comes from a select, it explose the processing time... even if the select time is very very small.
Is there a way to optimize the query to get similar results ?
I expected, in the worst case, that queries 2 and 3 could be 2 times slower than the 1 because they are performing 2 times about the same query... but 1700 times is very difficult to understand.
The explain seems to show that the index are not used with the subquery:
mysql> explain select * from documents where ID in (299);
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | documents | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from documents where ID in (select ID from documents where ID in (179));
+----+--------------------+-----------+-------+---------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-------+---------------+---------+---------+-------+--------+-------------+
| 1 | PRIMARY | documents | ALL | NULL | NULL | NULL | NULL | 103020 | Using where |
| 2 | DEPENDENT SUBQUERY | documents | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+--------------------+-----------+-------+---------------+---------+---------+-------+--------+-------------+
2 rows in set (0.00 sec)
I've also tried the same with force index(primary) but it does not help:
select * from documents force index(primary) where ID in (select ID from documents where ID in (199))
1 row in set (0.82 sec)
Thank you for your help
Subject
Views
Written By
Posted
Unexplained very bad subquery performance
1730
July 18, 2016 04:29AM
918
July 18, 2016 08:44AM
1033
July 18, 2016 01:06PM
853
July 18, 2016 03:30PM
953
July 19, 2016 03:16AM
855
July 19, 2016 04:21AM
877
July 20, 2016 04:35AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.