CODEWITHSUNDEEP
phpmysqloptimization
Raja Amer Khan
Raja Amer Khan

Reputation: 1519

Need to optimize MySQL query for IN statement

I want to send bulk email to my site users and the email will be sent to lets say 100k+ users at a time. What I want to achieve is to keep record of my mail() function calls i.e. as soon as the mail is sent to the user, an entry is added for that user in the temporary table. This helps me in condition if my server crashes then I can send the emails to the rest of the users.

Here's my problem:

I select the records from the users table where the records are NOT IN (select sent_ids from temp_table)

If there are to many sent IDs, let's say 70% of the total users, then it will make the query relatively slow.

What can I do to solve my problem?

Upvotes: 0

Views: 76

Answers (4)

DaveH
DaveH

Reputation: 7335

Sounds like a job for an outer join:

SELECT * FROM users u 
LEFT JOIN temp_table t
    ON u.id = t.id
WHERE t.id IS NULL

This will list all the users that have not been sent an email.

Upvotes: 1

paxal
paxal

Reputation: 851

Two options :

  1. newer versions of mysql (5.6, or mariadb 5.5) should deal with this request much better https://blog.mozilla.org/it/2013/01/29/in-subqueries-in-mysql-5-6-are-optimized-away/
  2. You can use an JOIN statement : SELECT users/* FROM users JOIN temptable ON send_id = user_id

Upvotes: 1

Zoltán Tamási
Zoltán Tamási

Reputation: 12809

It should not be slower than other variants, because in most cases MySQL will optimize the IN clauses as much as possible (at least later versions). However, you could try LEFT JOINing the temp table by the id, and then check for sent_id IS NULL to get the users who you didn't send your mail to already.

Upvotes: 1

Stephan
Stephan

Reputation: 8090

Have a look at EXIST/NOT EXISTS optimizations in mysql , docs.

Upvotes: 1

Related Questions