CODEWITHSUNDEEP
mysqlsubquery
user2081876
user2081876

Reputation: 57

MYSQL sub query performance inconsistency

G-day,

I'm somewhat puzzled by the following. When I run this query it takes a very long time to resolve:

Select *
from test
where id in (select id from test2 where customer = 'HARRY');

When I run the sub query by itself it resolves to '13454' in a spit second. When I change the main query's where statement to in ('13454') it also resolves instantly.

Someone have any idea why it won't run in it's original format??

Cheers,

Rene

Upvotes: 0

Views: 643

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The reason is a quirk of MySQL. The subquery is executed for each row of the outer table.

To fix this, use exists:

Select *
from test
where exists (select id from test2 where test2.customer = 'HARRY' and test2.id = test.id);

This will work most efficiently if you have an index on test2(customer, id).

I should add that this depends on the version of MySQL (I think it is fixed in 5.5). The older documentation explains it as:

Consider the following subquery comparison:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer expression outer_expr, and then runs the subquery and captures the rows that it produces.

Upvotes: 1

John Woo
John Woo

Reputation: 263683

use JOIN

SELECT  DISTINCT a.*
FROM    Test a
        INNER JOIN Test2 b
            ON a.ID = b.ID
WHERE   b.customer = 'Harry'

For faster performance, make sure that ID on both tables have KEY on them.

Upvotes: 0

Related Questions