CODEWITHSUNDEEP
mysql
Kunal
Kunal

Reputation: 1024

mysql IN query problem

select docid  from A  where  docid IN ( select distinct(docid) from B)

When I execute above query in mysql it takes 33 seconds, which is too long as per the size of data.

Below is the details of both tables.

   Table A :
   | docid       | int(11)  | NO   | PRI | NULL    |       |
   Total number of entries = 500 (all entries are unique)

   Table B:
   | docid       | int(11)  | YES  |     | NULL    |       |
   Total number of entries = 66508
   (number of unique entries are 500)

   mysql version : 5.2

If I execute only select docid from A it will take 0.00 seconds, while select docid from B is taking 0.07 seconds.

Then why IN query with subquery takes 33 seconds? Am I doing something wrong?

Upvotes: 1

Views: 108

Answers (2)

Brian
Brian

Reputation: 6450

MySQL doesn't handle IN (subquery) well. It executes the inner query every single time the outer query is evaluated, rather than "remembering" the results.

Hence you are much better doing a join.

Other RDBMSes don't do this btw.

Upvotes: 4

Bohemian
Bohemian

Reputation: 425033

The IN list is very large - 60K entries. You would be better to use a join:

select A.docid -- edited - I left out the A. :(
from A
join B on B.docid = A.docid;

This should execute very quickly and will give you the same result as your "IN" query.

Upvotes: 6

Related Questions