speeding up outer join query for newsletter script

Question

Hi,

I've got a PHP script that sends newsletters to a bunch of email adresses, it sends 15000 newsletters an hour, until all email adresses have been processed.

Because this process isn't run in one time, I have a table where I register all the adresses which have been mailed, so I can always easily get the unmailed adresses.

Here is a simplified example of what I do:

 select a.* 
 from email_all a left outer join email_send s 
      on s.email_id = a.id 
      and s.mailing_id = 1 
 where s.id IS null 
 limit 0, 15000

So all the adresses who haven't been mailed yet are selected, now the problem is, the more newsletters have been sent, the more adresses that are logged in the 'email_send' table and the slower the query gets. In the beginning there are 50.000 adresses in the table 'email_all', and in 'email_send' there are none, after a few ours there still are 50.000 adresses in table 'email_all', and also 50.000 adresses in 'email_send', and then all hell breaks lose. The scripts works perfectly for smaller mailings, like 10.000.

I've placed an index on the column 'email_id' in the second table, primary keys have been set.

Is there anyway I can speed it up? I've used 'NOT EXISTS IN' too but that doesn't help :(

Anyone can help?

Thx!!

Steve

Answer 1

Why don't you making it the reverse way:

• Create a large table with your queue
• Delete each row from the table which sent

Answer 2

Don't log email addresses that have been sent in other table. Just make another field in email_all table, tinyint(1) sent, and just update that field when email for that user is sent. Or make one large queue table and delete rows from there when they are sent.