title: Slow MySQL joins with inner select
body: <p>The following query works, but becomes slower as the size of the sendlog table increases over time. The goal is to select a list of all subscribers from the newsletter_subscribers table which <em>do not</em> have an email entry for a given newsletter id in the newsletter_sendlog table. Currently, it's taking about 2.2 seconds on my mysql server with only a couple of thousand entries in the sendlog. </p>
  
  <pre><code>SELECT `newsletter_subscribers`.* 
  FROM `newsletter_subscribers`
      INNER JOIN `newsletter_to_subscriber` 
          ON newsletter_to_subscriber.subscriber_id = newsletter_subscribers.id
      LEFT JOIN (
          SELECT `newsletter_sendlog`.`subscriber_email` 
          FROM `newsletter_sendlog` 
          WHERE (newsletter_id='7')
        ) AS `sendlog` 
          ON newsletter_subscribers.email = sendlog.subscriber_email 
  WHERE (sendlog.subscriber_email IS NULL) 
  AND (newsletter_to_subscriber.newsletter_id = '7')
  </code></pre>
  
  <p>EXPLAIN(query) outputs the following:</p>
  
  <p><a href="https://i.stack.imgur.com/7eeTN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7eeTN.png" alt="Output of EXPAIN"></a></p>
  
  <p>I'm not too familiar with the output of EXPLAIN, but if I read it correctly it would suggest that it's not using the index which I've defined on newsletter_sendlog.subscriber_email. I've tried using USE INDEX(email) on that table, but it does not seem to take effect. </p>
  
  <p>Any suggestions on how to optimize this? Or possibly suggest another query that does the same? </p>
  
  <hr>
  
  <p>The create table for newsletter_sendlog:</p>
  
  <pre><code>CREATE TABLE `newsletter_sendlog` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `subscriber_email` varchar(100) NOT NULL default '',
    `newsletter_id` int(11) default NULL,
    `sendstatus` int(11) default NULL,
    `senddate` timestamp NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY  (`id`),
    KEY `newsletter_id` (`newsletter_id`),
    KEY `email` (`subscriber_email`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2933 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>create table for newsletter_subscribers:</p>
  
  <pre><code>CREATE TABLE `newsletter_subscribers` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `email` varchar(100) NOT NULL default '',
    `name` tinytext,
    PRIMARY KEY  (`id`),
    KEY `email` (`email`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2964 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>create table for newsletter_to_subscriber:</p>
  
  <pre><code>CREATE TABLE `newsletter_to_subscriber` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `newsletter_id` int(11) NOT NULL,
    `subscriber_id` int(11) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `newsletter_subscriber` (`newsletter_id`,`subscriber_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2964 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <h2>Update:</h2>
  
  <p>Create table for newsletter_to_subscriber now looks like this after adding index on subscriber_id:</p>
  
  <pre><code>CREATE TABLE `newsletter_to_subscriber` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `newsletter_id` int(11) NOT NULL,
    `subscriber_id` int(11) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `newsletter_subscriber` (`newsletter_id`,`subscriber_id`),
    KEY `subscriber` (`subscriber_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=2964 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>The explain for the query suggested by @nobody:</p>
  
  <p><img src="https://i.stack.imgur.com/XuPXO.png" alt="Explain for nobody&#39;s query"></p>