CODEWITHSUNDEEP
phpmysqlsqlselect
stwhite
stwhite

Reputation: 3275

Speeding up WHERE NOT IN MySQL Query

What I'm trying to do is speed up this query. Currently it's about 15+ seconds slow. There are indexes on both r.email and v.sent as well as r.id, r.viewed and r.saved. EXPLAIN is showing both keys are in use, however I'm seeing that it's using Using where; Using temporary; Using filesort.

Any ideas on improving this query?

SELECT r.id, r.top, r.email
FROM request as r
WHERE r.viewed = 0 AND r.saved = 0 AND r.email NOT IN ( 
    SELECT v.sent FROM viewing as v WHERE v.sent = r.email
)
GROUP BY r.email 
ORDER BY r.top desc, r.date
LIMIT 100;

Upvotes: 2

Views: 1303

Answers (4)

Omesh
Omesh

Reputation: 29101

Use LEFT JOIN instead of NOT IN: why..? see here

SELECT r.id, r.top, r.email
FROM request as r
     LEFT JOIN viewing v
        ON r.email= v.sent
WHERE r.viewed = 0 AND 
      r.saved = 0 AND 
      v.sent IS NULL
GROUP BY r.email
ORDER BY r.top DESC, r.date
LIMIT 100;

For best performance, consider adding following indexes on tables, preferably covering indexes:

ALTER TABLE request ADD INDEX ix1 (email, viewed, saved, top, date);

ALTER TABLE viewing ADD INDEX ix1 (sent);

Upvotes: 2

Zefira
Zefira

Reputation: 4519

Apparently MySQL has subquery performance issues.

Luckily, there are ways to get around this. Specifically, transforming your subqueries into joins, or in your particular case into an exclusion join.

Upvotes: 0

Dan Grossman
Dan Grossman

Reputation: 52372

SELECT
  r.id,
  r.top,
  r.email
FROM
  request r
LEFT OUTER JOIN
  viewing v
ON
  v.sent = r.email
WHERE
  r.viewed = 0
AND
  r.saved = 0
AND
  v.sent IS NULL
GROUP BY
  r.email
ORDER BY
  r.top DESC, r.date
LIMIT 100

Upvotes: 0

juergen d
juergen d

Reputation: 204784

MySQL uses a temporary table for multiple records in an IN clause. Try

SELECT r.id, r.top, r.email
FROM request as r
left outer join viewing v on v.sent = r.email
WHERE r.viewed = 0 AND r.saved = 0 AND v.sent is null
GROUP BY r.email 
ORDER BY r.top desc, r.date
LIMIT 100;

Upvotes: 3

Related Questions