Introduction to the optimization examples of MYSQL in and EXISTS _mysql

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

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

SELECT * from "A where ID" (select ID from b) is
equivalent to: For
select IDs from B for
select * from a WHERE a.id = b.ID

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

SELECT * from a where exists (select 1 from b where b.id = a.id) is
equivalent to for
select * from a for
select * from B W Here b.ID = a.ID

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

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

For example:

/** execution Time: 0.313s **/
Select Sql_no_cache * from Rocky_member m where EXISTS (select 1 from Rocky_vip_appro a where m.i D = a.user_id and a.passed = 1);
/** execution Time: 0.160s **/
Select Sql_no_cache * from Rocky_member m WHERE m.id in (SELECT ID from Rocky_vip_appro WHERE Pass ed = 1);

Not in and not exists usages are similar.