MySQL Forums :: Performance :: Unexplained very bad subquery performance


Advanced Search

Unexplained very bad subquery performance
Posted by: Arnaud Bertrand ()
Date: July 18, 2016 04:29AM

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

Options: ReplyQuote


Subject Views Written By Posted
Unexplained very bad subquery performance 488 Arnaud Bertrand 07/18/2016 04:29AM
Re: Unexplained very bad subquery performance 313 Peter Brawley 07/18/2016 08:44AM
Re: Unexplained very bad subquery performance 385 Arnaud Bertrand 07/18/2016 01:06PM
Re: Unexplained very bad subquery performance 285 Peter Brawley 07/18/2016 03:30PM
Re: Unexplained very bad subquery performance 302 Arnaud Bertrand 07/19/2016 03:16AM
Re: Unexplained very bad subquery performance 292 Peter Brawley 07/19/2016 04:21AM
Re: Unexplained very bad subquery performance 288 james wang 07/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.