Comparison of not in, not exists and join is null efficiency in mysql

When I was just learning php, I asked a friend of mine about the functions of not in, not exists and join is null in mysql. how are their performance better, next I will perform a simple test.

Query all records that do not appear in the joined field in Table B from Table a. Currently, there are about 20 thousand records in both data tables, table B has 230 fewer records than table a. Therefore, you need to query the multiple records in Table a. The associated field is the id of table a and the aid of Table B, three Common query methods are used:

The Code is as follows:	Copy code
Select a. id from a left join B ON a. id = B. aid where B. aid is NULL; Select a. id from a where a. id not in (select B. aid from B ); Select a. id from a where not exists (select null from B where B. aid = a. id );

Result of using the is null method of join: 230 rows in set (39 min 0.48 sec)

Result of not in method: 230 rows in set (38 min 7.48 sec)

Not exists Method Result: 230 rows in set (37 min 52.44 sec)

Dizzy, why is it so slow? Where is the error?

In the past, the aid in table B was not indexed, And it was much faster to query the index after it was created. After the index was searched, only 0.52sec was used for the results, there will be such a strong difference between indexes and no indexes, so for such queries, you must create an index for the associated fields. Otherwise, you will have to accept!