Does mysql optimize the IN clause

Question

When i execute this mysql query like

select * from t1 where colomn1 in (select colomn1 from t2) ,

what really happens?

I want to know if it executes the inner statement for every row?

PS: I have 300,000 rows in t1 and 50,000 rows in t2 and it is taking a hell of a time.

make your column1 index or unique field in table t1 and column1 in table t2, it will help a lot — uvais, May 19 '14 at 10:41
how didnt work? was slow? returned different data? as uvais mentioned indexing will help for joins and for subqueries — Milan Halada, May 19 '14 at 10:43
possible duplicate of [Subquery v/s inner join in sql server](http://stackoverflow.com/questions/14052596/subquery-v-s-inner-join-in-sql-server) — Milan Halada, May 19 '14 at 10:45
@Uriel_SVK it was slow...i waited for 2-3 minutes...then i canceled the query.. — user217869, May 19 '14 at 11:34
@user217869 are you using any indexes? You should have indexes at least on `t1.column1` and `t2.column1`. Also it should be better to use `JOIN` — Milan Halada, May 19 '14 at 11:40
indexing works, but i want to know what really happens when this query gets executed !! — user217869, Apr 05 '15 at 05:13

score 1 · Answer 1 · answered May 31 '14 at 09:33

I'm flabbergasted to see that everyone points out to use JOIN as if it is the same thing. IT IS NOT!, not with the information given here. E.g. What if t2.column1 has doubles ?

=> Assuming there are no doubles in t2.column1, then yes, put a UNIQUE INDEX on said column and use a JOIN construction as it is more readable and easier to maintain. If it is going to be faster; that depends on what the query engine makes from it. In MSSQL the query-optimizer (probably) would consider them the same thing; maybe MySQL is 'not so eager' to recognize this... don't know.

=> Assuming there can be doubles in t2.column1, put a (non-unique) INDEX on said column and rewrite the WHERE IN (SELECT ..) into a WHERE EXISTS ( SELECT * FROM t2 WHERE t2.column1 = t1.column1). Again, mostly for readability and ease of maintenance; most likely the query engine will treat them the same...

The things to remember are

Always make sure you have proper indexing (but don't go overboard)
Always realize that what really happens will be an interpretation of your sql-code; not a 'direct translation'. You can write the same functionality in different ways to achieve the same goal. And some of these are indeed more resilient to different scenarios.

If you only have 10 rows, pretty much everything works. If you have 10M rows it could be worth examining the query plan... which most-likely will be different from the one with 10 rows.

score 0 · Answer 2 · answered May 19 '14 at 10:44

0

A join would be quicker, viz:

select t1.* from t1 INNER JOIN t2 on t1.colomn1=t2.colomn1

answered May 19 '14 at 10:44

Philip Sheard

5,789
5
27
42

score 0 · Answer 3 · answered May 19 '14 at 10:45

0

Try with INNER JOIN

SELECT t1.*
FROM t1
INNER JOIN t2 ON t1.column1=t2.column1

answered May 19 '14 at 10:45

Sadikhasan

18,365
21
80
122

score 0 · Answer 4 · answered May 19 '14 at 11:04

0

You should do indexing in column1 and then you can use inner join for indexing

CREATE INDEX index1 ON t1 (col1);
CREATE INDEX index2 ON t2 (col2);
select t1.* from t1 INNER JOIN t2 on t1.colomn1=t2.colomn1

answered May 19 '14 at 11:04

Ronak Shah

1,539
2
13
20

Does mysql optimize the IN clause

4 Answers4