4

Have a look at these two queries:

Simple:

mysql> SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput = (SELECT DISTINCT lastoutput FROM omgenvelope WHERE lastinput=18658);
+------------+-------+--------------------+-------------+---------+----------+
| idomgbatch | rutin | batchtime          | idomgoutput | flowmod | omgbatch |
+------------+-------+--------------------+-------------+---------+----------+
|      12174 |     8 | 20140508040930-832 |       10728 |     162 |    12174 |
+------------+-------+--------------------+-------------+---------+----------+
1 row in set (0.00 sec)

and

Complex:

mysql> SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput IN (SELECT DISTINCT lastoutput FROM omgenvelope WHERE lastinput=18658);
+------------+-------+--------------------+-------------+---------+----------+
| idomgbatch | rutin | batchtime          | idomgoutput | flowmod | omgbatch |
+------------+-------+--------------------+-------------+---------+----------+
|      12174 |     8 | 20140508040930-832 |       10728 |     162 |    12174 |
+------------+-------+--------------------+-------------+---------+----------+
1 row in set (7.40 sec)

In this case I knew that the subquery returns only 1 value (12174) so I could use the = operator for the subquery result set and get the answer in 0.00 seconds.

When I keep the IN operator, it takes a whole 7.4 seconds. I have changed my code into a loop for the subquery and a simple = in the "main" select, and all is well, but I'd still like to understand the mechanics behind this.

I'm too novice to understand all the details from the EXPLAIN command.

The query with IN lists three rows with select_type PRIMARY, PRIMARY, and DEPENDENT SUBQUERY, with corresponding row counts 10172, 1, and 721. Corresponding "keys": NULL, PRIMARY, fk_omgenvelope_omginput1_idx.

The = version that can only match 1 value instead does select_types PRIMARY, PRIMARY and SUBQUERY with row counts 1, 1, and 721.

There are 10114 rows in table omgoutput, 1854182 rows in table omgenvelope and 10108 rows in table omgbatch, so I don't understand what the 10172 in the complex EXPLAIN output comes from.

For completeness, here are the full EXPLAINations of the query plans:

Simple:

+----+-------------+-------------+-------+------------------------------------+------------------------------+---------+-------+------+------------------------------+
| id | select_type | table       | type  | possible_keys                      | key                          | key_len | ref   | rows | Extra                        |
+----+-------------+-------------+-------+------------------------------------+------------------------------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | omgoutput   | const | PRIMARY,fk_omgoutput_omgbatch1_idx | PRIMARY                      | 4       | const |    1 |                              |
|  1 | PRIMARY     | omgbatch    | const | PRIMARY                            | PRIMARY                      | 4       | const |    1 |                              |
|  2 | SUBQUERY    | omgenvelope | ref   | fk_omgenvelope_omginput1_idx       | fk_omgenvelope_omginput1_idx | 4       |       |  721 | Using where; Using temporary |
+----+-------------+-------------+-------+------------------------------------+------------------------------+---------+-------+------+------------------------------+

Complex:

+----+--------------------+-------------+--------+------------------------------------------------------------+------------------------------+---------+-----------------------------+-------+------------------------------+
| id | select_type        | table       | type   | possible_keys                                              | key                          | key_len | ref                         | rows  | Extra                        |
+----+--------------------+-------------+--------+------------------------------------------------------------+------------------------------+---------+-----------------------------+-------+------------------------------+
|  1 | PRIMARY            | omgoutput   | ALL    | fk_omgoutput_omgbatch1_idx                                 | NULL                         | NULL    | NULL                        | 10173 | Using where                  |
|  1 | PRIMARY            | omgbatch    | eq_ref | PRIMARY                                                    | PRIMARY                      | 4       | tracksdb.omgoutput.omgbatch |     1 |                              |
|  2 | DEPENDENT SUBQUERY | omgenvelope | ref    | fk_omgenvelope_omginput1_idx,fk_omgenvelope_omgoutput1_idx | fk_omgenvelope_omginput1_idx | 4       | const                       |   721 | Using where; Using temporary |
+----+--------------------+-------------+--------+------------------------------------------------------------+------------------------------+---------+-----------------------------+-------+------------------------------+

How shall I think when parsing this? What is the difference between DEPENDENT SUBQUERY and just SUBQUERY without dependent?

It doesn't seem to be just the IN and more than 1 potential row in the subquery that does it. Replacing the subquery with several constant values is also quick:

mysql> explain SELECT * FROM omgbatch JOIN omgoutput ON (idomgbatch=omgbatch) WHERE idomgoutput IN (10728,10729,10000,10,100,0,23123);
+----+-------------+-----------+--------+------------------------------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table     | type   | possible_keys                      | key     | key_len | ref                         | rows | Extra       |
+----+-------------+-----------+--------+------------------------------------+---------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | omgoutput | range  | PRIMARY,fk_omgoutput_omgbatch1_idx | PRIMARY | 4       | NULL                        |    7 | Using where |
|  1 | SIMPLE      | omgbatch  | eq_ref | PRIMARY                            | PRIMARY | 4       | tracksdb.omgoutput.omgbatch |    1 |             |
+----+-------------+-----------+--------+------------------------------------+---------+---------+-----------------------------+------+-------------+
  • 2
    The optimizer cannot know in advance that the sub-query used for the IN comparison only returns a single row. So it generates a plan that could potentially handle multiple values. When using the equality operator you tell the optimizer "I know that there will only be a single value" and thus it can handle the comparison differently – a_horse_with_no_name May 8 '14 at 15:27
  • You may want to experiment with MySQL 5.6 and MariaDB 5.5 and see how the plans and the execution times will vary. They have both implemented some (but different) optimizations in the query planner. – ypercubeᵀᴹ May 8 '14 at 16:43
  • 1
  • Have you run an explain on just your subquery: SELECT DISTINCT lastoutput FROM omgenvelope WHERE lastinput=18658 and timed it's performance? SELECT DISTINCT is often a slow query – Peter Smith May 8 '14 at 17:37
  • Also, why are you appending with a where clause and not making it a condition of the join with an AND? – Peter Smith May 8 '14 at 17:42
3

According to this answer:

This is a known problem in MySQL. Rumors say it will be fixed.

The problem is due to a missing optimization when a subquery using IN is incorrectly indentified as dependent subquery instead of an independent subquery.

This causes the subquery to be run a huge number of times, which is unnecessary.

  • 1
    That answer was precise in 2010 but you should correct your answer. There is no MySQL 6.0 - not anymore. The current version is 5.6 and the next will be 5.7 – ypercubeᵀᴹ May 21 '14 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.