MySQL Sub-query optimization

Today to query for seven days there are no installed stores information, the first thought of using not in, first installed the UserID check out, and then the ID not in, but this must use sub-query, the data volume is small can also, the data volume is large, the efficiency is particularly low, Because MySQL needs to establish a temporary table for the query results of the inner query statement. The outer query statement then queries the record in the temporary table. After the query is complete, MySQL needs to revoke these temporary tables. As a result, the speed of the subquery is affected somewhat. If the amount of data queried is large, the effect increases accordingly.

First Use:

SELECT * from V9_wba_account where levels = 3 and ID not in (SELECT distinct userid from V9_wba_dev where > 2015091 7)

Then try:

SELECT * from V9_wba_account as user left join V9_wba_dev as Dev on user.id=dev.userid where levels = 3

Because the left JOIN does not satisfy the User.id=dev.userid, it also queries the data in the table, but the fields in the right table in the result are null.

At last:

SELECT * from V9_wba_account as user-left join V9_wba_dev as Dev in user.id=dev.userid where levels = 3 and dev.id is NULL

Finally, it is correct to exclude the right table from being null.

SELECT * from V9_wba_account as user left join V9_wba_dev as Dev on user.id=dev.userid and Dev.days >= 201 50918 where levels = 3 and dev.id is NULL;

On is followed by the query criteria for the right table

MySQL Sub-query optimization