The best way to get a table of data not in another table (Join and exists efficiency study)

Business Requirements:

The company has a CRM broker management system, the day before yesterday, the more difficult problem is to query a table in the other table is not on all users and pagination display, but the problem is the appearance of large amounts of data, if not in the (select.), not EXISTS (select ...) Such sub-query needs to make all the information in the query table filter, a single query data volume is too large efficiency problem came, so privately checked a bit of information, and according to the examples provided in the data set up a corresponding test table did some testing, Then the problem-solving approach to the technical Department of the Shong colleagues to solve the problem, because it is the day before yesterday, it is a busy time to write a log, is this thing has not been a note has been two days sleep well, so today came or do a note, share to everyone's good, if you have a better way or Say there are bug questions, please feel free to contact the Sky star [email protected]

Here is a set of tests for join, EXISTS efficiency comparison

Open the profiling first to see the SQL execution time

Set profiling=1;

Comparison between exists subquery and join join efficiency

EXPLAINSELECTFILM_ID, language_id fromSakila.filmWHERE  not EXISTS(    SELECT *  fromSakila.film_actorWHEREfilm_actor.film_id=film.film_id);

EXPLAINSELECTFILM_ID, language_id fromSakila.film Left JOINsakila.film_actor USING (film_id)WHEREfilm_actor.actor_id is NULL;

They only have a slight difference, a select_type, and extra the latter with not exists, that is, the early termination algorithm, when the first actor_id is not NULL, discard the filter.

Finally, let's look at the efficiency.

It is known that the use of sub-query exists is less efficient.

SELECT DISTINCTfilm_id fromSakila.filmJOINsakila.film_actor USING (film_id);SELECTfilm_id fromSakila.filmWHERE EXISTS (    SELECT *  fromSakila.film_actorWHEREfilm.film_id=film_actor.film_id); SHOW PROFILES;

PS: The episode of "Picking up stars in the sky," some say that when you use the Join method to manipulate a table of  1000 data, the speed becomes slow, In fact, the problem is not the SQL statement itself, single table 10 million data slow problem is disk IO caused by the mishap, is any optimization unavoidable, using any software layer optimization can not avoid the disk IO problem to the database caused by the mishap.

summary: Judging the data of one table is not in another table the best method is optional (Prerequisites: Aid for Table A and aid for table B must be indexed, B.aid cannot have a null value):
SELECT Aid from a left JOIN b on a.aid = B.aid WHERE b.aid is NULL LIMIT 0,100;

select  aid from a Left join b USING (aid) WHERE B.aid is null  LIMIT 0,100, (this syntax is the same as in the short form of the SQL statement above, the using language See the end of this article ↓)

and SELECT aid from a to go to A.aid=b.aid where B.aid is Null only returns data with the aid of a table aid and B aid being equal while B table is also null, while the data that satisfies this condition has only one case, That is, if the data is not met in the B table, then the b.aid is null and equal to A.aid, which is the data we are looking for in table A but not in table B.
 
using usage and effects are as follows:

Given join conditions for table joins (can be understood as shorthand)

SELECT * from a JOIN b on a.id = b.ID;

Use using can be written as

SELECT * from a JOIN b USING (ID);

??????

The best way to get a table of data not in another table (Join and exists efficiency study)