CODEWITHSUNDEEP
mysqlquery-optimizationsubquery
Lucas
Lucas

Reputation: 885

MySQL Subquery Optimization

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);

Upvotes: 1

Views: 475

Answers (1)

Galz
Galz

Reputation: 6832

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);

Upvotes: 2

Related Questions