MYSQL in vs EXISTS optimization example

Article reprinted from: Http://www.jb51.net/article/53127.htm

When the data set of table B must be less than the data set of Table A, the in is better than exists, when the data set of table A is less than the data set of Table B, the exists is better than in

Optimization principle: Small table drive Large table, that is, small data sets drive large datasets.

############# principle (RBO) #####################

?

1234	select * from a where id in ( select id from b) for select id from b for select * from a Code class= "SQL keyword" >where a.id = b.id

When the data set of table B must be less than the data set of table A, in is better than exists.

?

1234	select * from a where exists ( select 1 Code class= "SQL keyword" >from b where b.id = a.id) for select * from a for select * from b where b.id = a.id

When the data set of a table is less than the data set of Table B, the exists is better than in.

Note: The ID field of table A and B should be indexed.

For example:

?

1234	/** 执行时间：0.313s **/SELECT SQL_NO_CACHE * FROM rocky_member m WHERE EXISTS (SELECT 1 FROM rocky_vip_appro a WHERE m.ID = a.user_id AND a.passed = 1);/** 执行时间：0.160s **/SELECT SQL_NO_CACHE * FROM rocky_member m WHERE m.ID in(SELECT ID FROM rocky_vip_appro WHERE passed = 1);

Not in and not exists usages are similar.

MYSQL in vs EXISTS optimization example