MySQL Subquery Optimization

Question

The Query:

SELECT a FROM table1 WHERE b IN (SELECT b FROM table1 WHERE c = 2);

If the subquery returns zero results, mysql takes a long time to realize it before it finally returns an empty result. (2.5s when subquery is empty, 0.0005s when there is a subquery result).

My question: is there a way to modify the query such that it will still return an empty result but take the same time as it did when there was a result?

I tried:

SELECT a FROM table1 WHERE b IN ((SELECT b FROM table1 WHERE c = 2), 555);

...but it only works WHEN the subquery is empty. If there is a result, the query fails.

-- I don't want to change the query format from nested to join/etc.

Ideas..? Thanks!

-- EDIT: Also, I forgot to add: The subquery will likely result in a decent-sized list of results, not just one result. --- ALSO, when I type '555', I am referring to a value that will never exist in the column.

-- EDIT 2: I also tried the following query and it "works" but it still takes several orders of magnitude longer than the original query when it has results:

SELECT a FROM table1 WHERE b IN (SELECT 555 AS b UNION SELECT b FROM table1 WHERE c = 2);

Galz · Accepted Answer · 2011-09-29 17:39:29Z

2

Wild guess (I can't test it right now):

SELECT a FROM table1 WHERE
EXISTS (SELECT b FROM table1 WHERE c = 2)
AND b IN (SELECT b FROM table1 WHERE c = 2);

edited Sep 29, 2011 at 17:39

answered Sep 29, 2011 at 16:55

Galz

6,8525 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Lucas Over a year ago

Ah, very nice thinking. That works as well, but unfortunately it still suffers the same problem as my [edit 2] attempt: it takes several orders of magnitude longer than the original query when it has results.

Galz Over a year ago

@Lucas - weird. Is running the inner query alone also much longer when no data is returned? Plus - is b indexed in table1, and is c indexed?

Lucas Over a year ago

weird indeed! Yes, running the inner query alone has the same inflation in time when a value doesn't exist. And yes, all 3 columns are individually indexed. -- Side note: I like your query better than my [edit 2] query, so if nothing else I will definitely use that and suffer the time inflation.

Lucas Over a year ago

SPOT ON! Somehow I lost one of the indices in a previous transition. That has corrected the time issue. Thanks!

Karolis Over a year ago

Actually there is no need to use LIMIT inside EXISTS.

Collectives™ on Stack Overflow

MySQL Subquery Optimization

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related