Ggtcf

Bob has never been a M before

What will be the benefits of Brexit?

Reply ‘no position’ while the job posting is still there (‘HiWi’ position in Germany)

How to prevent YouTube from showing already watched videos?

Is there an Impartial Brexit Deal comparison site?

Is there a problem with hiding "forgot password" until it's needed?

Can somebody explain Brexit in a few child-proof sentences?

Books on the History of math research at European universities

Perfect riffle shuffles

Simple image editor tool to draw a simple box/rectangle in an existing image

Would it be legal for a US State to ban exports of a natural resource?

Can the harmonic series explain the origin of the major scale?

Calculating the number of days between 2 dates in Excel

Can a controlled ghast be a leader of a pack of ghouls?

Should my PhD thesis be submitted under my legal name?

Is there any significance to the Valyrian Stone vault door of Qarth?

A Standard Integral Equation

Why does this part of the Space Shuttle launch pad seem to be floating in air?

How can I successfully establish a nationwide combat training program for a large country?

Resetting two CD4017 counters simultaneously, only one resets

Invariance of results when scaling explanatory variables in logistic regression, is there a proof?

Female=gender counterpart?

Freedom of speech and where it applies

A car is moving at 40 km/h. A fly at 100 km/h, starts from wall towards the car(20 km away)flies to car and back. How many trips can it make?

Mysql 8.0 not exists vs not in vs left join vs where count(*)

INNER JOIN ON vs WHERE clauseHow to 'insert if not exists' in MySQL?What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?MySQL INNER JOIN of 3 tables with count and totalsMySQL Limit LEFT JOIN Subquery after joiningjdbc - Unable to store russian chinese arabic letters in mysqlJOIN Query In MYSQL and PHPMYSQL Error Installing BuildEnginesql import error, invalid default valuehow to optimize a select query with multiple left joins in a large database

-2

1

Mysql 8.0 not exists vs not in vs left join vs where count(*)

i don't know why not in is fast

A ############
 SELECT
 a.openid 
FROM
 user_all a 
WHERE
 a.openid NOT IN ( SELECT b.openid FROM `user` b );

B ###############

SELECT
 a.openid 
FROM
 user_all AS a 
WHERE
 ( SELECT COUNT( * ) AS num FROM `user` AS b WHERE a.openid = b.openid ) = 0

C ###########

SELECT
 a.openid
FROM
 user_all a
 LEFT JOIN `user` b ON a.openid = b.openid
WHERE
 b.openid IS NULL;

D #########

SELECT
 a.openid 
FROM
 user_all a 
WHERE
 NOT EXISTS ( SELECT * FROM `user` b WHERE a.openid = b.openid );

################

this is my table message

CREATE TABLE `user_all` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


CREATE TABLE `user` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


user_all table size 921W
user table size 373W

Explain results:

mysql

share|improve this question

edited Mar 8 at 10:06

LukStorms

14.1k31734

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the question?
  
  – Thomas G
  Mar 8 at 7:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know why not in is faster than not exists.
  
  – Eric_余浩
  Mar 8 at 8:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  People say that not in and in are slow, but I find that not in is the fastest of the four SQLs mentioned above.
  
  – Eric_余浩
  Mar 8 at 8:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's not a question
  
  – Strawberry
  Mar 8 at 8:04
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  For such a question you should show the explain plan of all queries and your table's structure with indexes. How can we deduct from what you posted, the performance of each query? We cant.
  
  – Thomas G
  Mar 8 at 8:04
  

  
  
  
  

 | 
show 2 more comments

-2

1

Mysql 8.0 not exists vs not in vs left join vs where count(*)

i don't know why not in is fast

A ############
 SELECT
 a.openid 
FROM
 user_all a 
WHERE
 a.openid NOT IN ( SELECT b.openid FROM `user` b );

B ###############

SELECT
 a.openid 
FROM
 user_all AS a 
WHERE
 ( SELECT COUNT( * ) AS num FROM `user` AS b WHERE a.openid = b.openid ) = 0

C ###########

SELECT
 a.openid
FROM
 user_all a
 LEFT JOIN `user` b ON a.openid = b.openid
WHERE
 b.openid IS NULL;

D #########

SELECT
 a.openid 
FROM
 user_all a 
WHERE
 NOT EXISTS ( SELECT * FROM `user` b WHERE a.openid = b.openid );

################

this is my table message

CREATE TABLE `user_all` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


CREATE TABLE `user` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


user_all table size 921W
user table size 373W

Explain results:

mysql

share|improve this question

edited Mar 8 at 10:06

LukStorms

14.1k31734

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the question?
  
  – Thomas G
  Mar 8 at 7:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know why not in is faster than not exists.
  
  – Eric_余浩
  Mar 8 at 8:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  People say that not in and in are slow, but I find that not in is the fastest of the four SQLs mentioned above.
  
  – Eric_余浩
  Mar 8 at 8:04
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's not a question
  
  – Strawberry
  Mar 8 at 8:04
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  For such a question you should show the explain plan of all queries and your table's structure with indexes. How can we deduct from what you posted, the performance of each query? We cant.
  
  – Thomas G
  Mar 8 at 8:04
  

  
  
  
  

 | 
show 2 more comments

Mysql 8.0 not exists vs not in vs left join vs where count(*)

i don't know why not in is fast

A ############
 SELECT
 a.openid 
FROM
 user_all a 
WHERE
 a.openid NOT IN ( SELECT b.openid FROM `user` b );

B ###############

SELECT
 a.openid 
FROM
 user_all AS a 
WHERE
 ( SELECT COUNT( * ) AS num FROM `user` AS b WHERE a.openid = b.openid ) = 0

C ###########

SELECT
 a.openid
FROM
 user_all a
 LEFT JOIN `user` b ON a.openid = b.openid
WHERE
 b.openid IS NULL;

D #########

SELECT
 a.openid 
FROM
 user_all a 
WHERE
 NOT EXISTS ( SELECT * FROM `user` b WHERE a.openid = b.openid );

################

this is my table message

CREATE TABLE `user_all` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


CREATE TABLE `user` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


user_all table size 921W
user table size 373W

Explain results:

mysql

share|improve this question

edited Mar 8 at 10:06

LukStorms

14.1k31734

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62

A ############
 SELECT
 a.openid 
FROM
 user_all a 
WHERE
 a.openid NOT IN ( SELECT b.openid FROM `user` b );

SELECT
 a.openid 
FROM
 user_all AS a 
WHERE
 ( SELECT COUNT( * ) AS num FROM `user` AS b WHERE a.openid = b.openid ) = 0

SELECT
 a.openid
FROM
 user_all a
 LEFT JOIN `user` b ON a.openid = b.openid
WHERE
 b.openid IS NULL;

SELECT
 a.openid 
FROM
 user_all a 
WHERE
 NOT EXISTS ( SELECT * FROM `user` b WHERE a.openid = b.openid );

CREATE TABLE `user_all` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


CREATE TABLE `user` (
 `openid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;


user_all table size 921W
user table size 373W

share|improve this question

edited Mar 8 at 10:06

LukStorms

14.1k31734

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62

share|improve this question

edited Mar 8 at 10:06

LukStorms

14.1k31734

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62

edited Mar 8 at 10:06

LukStorms

14.1k31734

edited Mar 8 at 10:06

LukStorms

14.1k31734

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62

asked Mar 8 at 7:52

Eric_余浩Eric_余浩

62