Difference: 1. In performs a hash connection query through the parent query table and the child self-query table, or performs a loop on the parent query table and then queries the child query table; 2. When there is no index or primary key In this case, as the amount of data behind in or or increases, the execution efficiency of or will decrease significantly, but the execution efficiency of in will not decrease significantly.
The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.
What is the difference between in and or in mysql
Do data comparison, tens of millions of levels of data
In sql statements The difference between in and or is: different operations, different suitability, and different execution efficiency.
1. Different operations
1. in: in is a hash connection between the parent query table and the child self-query table.
2. or: or performs a loop on the parent query table, and queries the child query table each time the loop loops.
2. Suitable for different 5261
1. in: in is suitable for situations where the child query table has more data than the parent query table.
2. or: or is suitable for situations where the child query table has less data than the parent query table.
3. Different execution efficiency
1. In: In the absence of an index, as the amount of data behind in increases, the execution efficiency of in will not drop much.
2. or: In the absence of an index, as the amount of data behind or increases, the execution efficiency of or will decrease significantly.
If the columns where in and or are located have indexes or primary keys, there is no difference between or and in, and the execution plan and execution time are almost the same.
If the columns where in and or are located do not have indexes, the performance difference will be huge. In the absence of an index, as the amount of data behind in or or increases, the efficiency of in will not drop much, but the performance of or will drop significantly as the number of records increases
Therefore When defining the efficiency of in and or, another condition should be added, that is, whether the column is indexed or whether it is a primary key. If there is an index or primary key, there is no difference in performance. If there is no index, the performance difference is not a little bit!
Recommended learning:mysql video tutorial
The above is the detailed content of What is the difference between in and or in mysql. For more information, please follow other related articles on the PHP Chinese website!