MySQL Forums
Forum List  »  Performance

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
543
July 18, 2016 04:29AM


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.