Detailed explanation of the difference between in and exists in MySQL

4. exists and in

What is the difference between in and exists? When should we use in and when should we use exists?

Let’s remember the formula first and then talk about the details! " If the outer query table is smaller than the subquery table, use exists. If the outer query table is larger than the subquery table, use in. If the outer and subquery tables are similar, use whichever you prefer. "

I think you have already seen that when the data in the fruits table is very large, it is not suitable to use in, because it will traverse all the data in the fruits table at most once.

For example, if the suppliers table has 10,000 records and the fruits table has 1,000,000 records, then the maximum number of traversals possible is 10,000*1,000,000 times, which is very inefficient.

For example, if the suppliers table has 10,000 records and the fruits table has 100 records, then the maximum possible traversal is 10,000*100 times, which greatly reduces the number of traversals and greatly improves efficiency.

However: the suppliers table has 10,000 records and the fruits table has 100 records, then exists() is still executed 10,000 times. It is better to use in() to traverse 10,000*100 times, because in() traverses in memory, and exists() needs to query the database. We all know that querying the database consumes higher performance, and memory is faster.

Therefore, we only need to remember the formula: "If the outer query table is smaller than the subquery table, use exists; if the outer query table is larger than the subquery table, use in; if the outer and subquery tables are similar, use whichever you like."

5. not exists and not in

Like exists, the id index on suppliers is used, exists() is executed the number of times fruits.length, and the result set of exists() is not cached.

Because not in is essentially equal to != and != ··· , and since != will not use the index, not in will not use the index.

Why doesn’t not in use the index?

We assume that there are 1 million data and s_id has only two values: 0 and 1. To use the index, we need to read the index file first, then perform a binary search to find the corresponding data disk pointer, and then use the corresponding data on the disk based on the read pointer, which affects the result set of 500,000. In this case, it is obvious which is faster than a direct full table scan.

If your s_id field is a unique one, the index will be used.

If you must use an index, you can use force index, but the efficiency will not improve and it will generally be slower.

Use indexes properly. Cardinality is an important indicator. If it is too small, it is the same as not having any indexes at all and it also wastes space.

Therefore, regardless of the size of suppliers and fruits, it is more efficient to use not exists.

This is the end of this article about the detailed explanation of the difference between in and exists in MySQL. For more information about the difference between MySQL in and exists, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!