CODEWITHSUNDEEP
mysqlquery-performance
user217869
user217869

Reputation: 21

Does mysql optimize the IN clause

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.

Upvotes: 0

Views: 1693

Answers (4)

deroby
deroby

Reputation: 6002

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.

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

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

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18601

Try with INNER JOIN

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

Upvotes: 0

Philip Sheard
Philip Sheard

Reputation: 5825

A join would be quicker, viz:

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

Upvotes: 0

Related Questions