[mysql]集合差を求める場合の処理速度比較(NOT IN(), NOT EXISTS, LEFT JOIN … IS NULL)

2017/06/13

http://qiita.com/Hiraku/items/71873bf31e503eb1b4e1

こういう所とか

http://rzpg.blogspot.jp/2011/02/mysqlnot-innot-exists.html

こういう所で、集合差をとる場合(Aの集合には存在しないレコードをとると。)の処理のパターンが紹介されており、

それぞれ速度差があるということで、手元で試してみた。

状況として、「当月のアクティブユーザーで、先月アクセスしていないユーザー」というイメージ

※いちおうキャッシュしないように「SQL_NO_CACHE」で。。

結果

NOT IN　　 14.96秒

NOT EXISTS 11.76 秒

LEFT JOIN 5.05 秒

LEFT JOIN に対し、NOT EXISTS は２倍　、NOT IN は３倍という感じ。

LEFT JOIN is NULLがとりあえずの手という所でしょうか。

参考 SQL

NOT IN(…)：

SELECT  SQL_NO_CACHE count(*) AS count
FROM members
WHERE  date BETWEEN '2017-06-01' AND  '2017-06-30'
  AND member NOT IN (
  SELECT member FROM members
  WHERE  date BETWEEN '2017-05-01' AND  '2017-05-31'
);

SELECT SQL_NO_CACHE count(*) AS count

FROM members

WHERE date BETWEEN '2017-06-01' AND '2017-06-30'

AND member NOT IN (

SELECT member FROM members

WHERE date BETWEEN '2017-05-01' AND '2017-05-31'

);

NOT EXISTS(….),

SELECT  SQL_NO_CACHE count(*) AS count
FROM members this
WHERE
  NOT EXISTS (
    SELECT member FROM members past
    WHERE
      this.member = past.member  
      AND date BETWEEN '2017-05-01' AND  '2017-05-30'      
  )
  AND date BETWEEN '2017-06-01' AND  '2017-06-02'
;

SELECT SQL_NO_CACHE count(*) AS count

FROM members this

WHERE

NOT EXISTS (

SELECT member FROM members past

WHERE

this.member = past.member

AND date BETWEEN '2017-05-01' AND '2017-05-30'

)

AND date BETWEEN '2017-06-01' AND '2017-06-02'

;

LEFT JOIN … IS NULL

SELECT  SQL_NO_CACHE count(*) AS count
FROM members this
LEFT JOIN (
  SELECT member FROM members
  WHERE  date BETWEEN '2017-05-01' AND  '2017-05-30'
) pre
  ON this.member = pre.member
WHERE  date BETWEEN '2017-06-01' AND  '2017-06-02'
AND pre.member IS NULL;