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 开发者_如何学Cis 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?

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.

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.