Unexplained very bad subquery performance

[Arnaud Bertrand]

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