I have following query on mysql, query logic is correct however issue is since there are over 10,000+ seeker emails and 24,000+ guest emails it take long time to execute, is there a better way to do this same this ?
SELECT g.email, g.name
FROM guest g
WHERE g.type='guest'
AND g.email NOT IN (SELECT email FROM seeker GROUP BY email)
GROUP BY g.email
Try this:
SELECT
g.email, g.name
FROM
guest g
LEFT JOIN
seeker s
ON
s.email = g.email
WHERE
g.type = 'guest'
AND
s.email IS NULL
GROUP BY
g.email;
LEFT JOIN. This format is faster than using a nested SELECT.
SELECT DISTINCT g.email, g.name
FROM guest g
WHERE g.type='guest'
AND NOT EXISTS (SELECT 1 FROM seeker s WHERE g.email = s.email)
And be sure you have an index on seeker.email, guest.type, guest.email, which would be awesome if the columns are NOT NULL on top of that.
NOT IN (SELECT).
SELECT DISTINCT g.email, g.name
FROM guest g
LEFT OUTER seeker s ON s.email = g.email
WHERE g.type='guest' AND s.email IS NULL
DISTINCT may have a different result. While GROUP BY g.email will ensure there are no repeated e-mails, DISTINCT will only ensure there are no repeated e-mail and name combinations. sqlfiddle.com/#!2/d94bf/3
SELECT. Just take a look at is sub select...I think he used GROUP but want the DISTINCT effect :) GROUP will remove data from the listYou don't need to group by inner query. You can add DISTINCT instead.
SELECT g.email, g.name
FROM guest g
WHERE g.type='guest'
AND g.email NOT IN (SELECT DISTINCT email FROM seeker)
GROUP BY g.email
even this will work
SELECT g.email, g.name
FROM guest g left outer join seeker s on g.email = s.email
WHERE g.type='guest'
AND s.email is null
GROUP BY g.email
There will be a lot of string comparisons in your query, it'd help if you index email in your tables esp. seeker.
Also, avoid using SELECT columns that are non-aggregated and not present in GROUP BY. The result is indeterminate.
The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.
More in manual.
SELECT email FROM seeker GROUP BY email when used in IN will produce same effect as SELECT distinct email FROM seeker got any examples/explanation?group by on outer query will still produce same result. Also, OP should't be using group by the way it is shown leaving the result to be implementation dependent.NOT IN as shown in this case.First, for your query, you don't need the group by:
SELECT g.email, g.name
FROM guest g
WHERE g.type = 'guest' AND g.email NOT IN (SELECT email FROM seeker)
GROUP BY g.email
That might be sufficient. With an index on seeker(email), the following should optimize ok:
SELECT g.email, g.name
FROM guest g
WHERE g.type = 'guest' AND
not exists (SELECT 1 FROM seeker where seeker.email = g.email)
GROUP BY g.email;
If you have lots of duplicates in most tables for email, then I wouldn't recommend a left join approach.
SELECT DISTINCT emailmight help as well, if there are many duplicate emails in seeker.GROUPwith not all non-aggregated column will remove entries from your list that you may be want...See SQLFIDDLEexplainon the query, you want to minimize "temporary" and "file" steps.