IN and EXITS efficiency IN mysql and mysqlINEXITS Efficiency

IN and EXITS efficiency IN mysql and mysqlINEXITS Efficiency

The in statement in mysql uses a hash connection between the external table and the internal table, while the exists statement uses a loop on the External table. Each loop then queries the internal table. We always think that exists is more efficient than in statements. This statement is actually inaccurate. This is to differentiate the environment.
 

If the two tables to be queried are of the same size, there is little difference between in and exists.. If one of the two tables is small and the other is a large table, exists is used for the large subquery table and in is used for the small subquery table: Example: Table A (small table) and Table B (large table) 1: select * from A where cc in (select cc from B) is inefficient and uses the index of the cc column in table; Select * from A where exists (select cc from B where cc = A. cc) is highly efficient and uses the index of the cc column in table B. Opposite 2: select * from B where cc in (select cc from A) is highly efficient and uses the index of the cc column in table B; Select * from B where exists (select cc from A where cc = B. cc) is inefficient and uses the index of the cc column in table.   Not in and not exists if the query statement uses not in, the internal and external tables are scanned for the whole table, and no index is used. However, the not extsts subquery can still use the table index. Therefore, whether the table is large, not exists is faster than not in.. Difference between in and = Select name from student where name in ('zhang ', 'wang', 'lil', 'zhao '); And Select name from student where name = 'zhang' or name = 'lil' or name = 'wang' or name = 'zhao' The results are the same.
What is the difference between mysql exists and in?

In is a hash connection between the external table and the internal table;
Exists loops the External table, and queries the internal table after each loop.

The argument that exists is more efficient than in is always inaccurate.
If the two tables to be queried are of the same size, there is little difference between in and exists.
If one of the two tables is small and the other is a large table, exists is used for the large subquery table, and in is used for the small subquery table.

Hope to help you.

How efficient is mysql in query?

IN is very slow, try to avoid. However, there are still no problems in the hundreds of thousands.