Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the difference between in and or in mysql". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the difference between in and or in mysql".

Differences: 1, in through the parent query table and child self-query table for hash join operation query, or is the parent query table for the loop cycle and then query the child query table; 2, in the absence of an index or primary key, with the increase in the amount of data behind in or or, the execution efficiency of or will decrease significantly, and 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 level data

The differences between in and or in sql statements are: different operations, different suitability, and different execution efficiency.

The operation is different.

1. In:in is a hash join between the parent query table and the child self-query table.

2. Or:or is a loop loop to the parent query table, and each loop loop queries the child query table.

Second, suitable for different 5261

1. In:in is suitable for 4102 situations where the data of the child query table is more than that of the parent query table.

2. Or:or is suitable for situations where the child query table has less data than the parent query table.

Third, different implementation efficiency

1. In: in the absence of an index, the execution efficiency of in will not decrease greatly with the increase of the amount of data behind in.

2. Or: in the absence of an index, the execution efficiency of or will decrease significantly with the increase of the amount of data behind or.

If in and or are listed with indexes or primary keys, there is no difference between or and in, and the execution plan and execution time are almost the same.

There is a big difference in performance if the columns where in and or are located do not have an index. In the absence of an index, the efficiency of in will not decline much with the increase of the amount of data behind in or or, but the performance of or will decline greatly as more records are recorded.

Therefore, when defining the efficiency of in and or, you should add another condition, that is, whether the column has an index or whether it is a primary key. If there is an index or primary key performance is no different, if there is no index, the performance difference is not a little bit!

Thank you for your reading, the above is "what is the difference between in and or in mysql?" after the study of this article, I believe you have a deeper understanding of the difference between in and or in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.