MySQL Subquery Optimization

Join 7000 others and follow Sean Hull on twitter @hullsean.

MySQL’s optimizer can do a lot of things, but subqueries are not always handled well. Take a look at the IN subquery below. If you see the DEPENDENT SUBQUERY in your explain plan, you may want to take a second look. This will run slow as a dog, when the tables get large.

[code]
SELECT * FROM bucket
WHERE bucket_id IN (
SELECT bucket_id
FROM bucket_items
WHERE item_id = 1);
[/code]

Here’s what the EXPLAIN looks like.

[code]
(sean@localhost:mysql.sock) [test]> explain select * from bucket where bucket_id in (select bucket_id from bucket_items where item_id = 1);
+----+--------------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | bucket | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 2 | DEPENDENT SUBQUERY | bucket_items | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+--------------------+--------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
[/code]

Also: 3 Ways to Optimize Paging queries with LIMIT and OFFSET

[quote]
Subqueries are not handled well in MySQL by default. Luckily an INNER JOIN rewrite can help in some cases.
[/quote]

Rewrite as an INNER JOIN

Fortunately there is an optimization for this type of query. You can rewrite it as an inner join.

[code]
SELECT bucket.*
FROM bucket
INNER JOIN bucket_items
USING (bucket_id)
WHERE item_id = 1;
[/code]

Here’s what the new explain looks like:

[code]
(sean@localhost:mysql.sock) [test]> explain select bucket.* from bucket inner join bucket_items using (bucket_id) where item_id = 1;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | bucket_items | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 1 | SIMPLE | bucket | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
[/code]

Also: How to optimize MySQL UNION for high speed

Note that you’re still getting out the same bucket items as the previous query, you’re just showing MySQL a much more efficient way to fetch and return the rows to you.

**Last point. You should index the bucket_id & item_id columns. I simply wanted to illustrate the DEPENDENT SUBQUERY above.

Get some in your inbox: Exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample