title: How do I improve performance on a MySQL query with NULL?
body: <p>I have several million records in the following table:</p>
  
  <pre><code>CREATE TABLE `customers` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `store_id` int(10) unsigned DEFAULT NULL,
    `first_name` varchar(64) DEFAULT NULL,
    `middle_name` varchar(64) DEFAULT NULL,
    `last_name` varchar(64) DEFAULT NULL,
    `email` varchar(128) DEFAULT NULL,
    `phone` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_store_email` (`store_id`,`email`),
    KEY `index_store_phone` (`store_id`,`phone`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p><strong>Query #1</strong> takes ~800ms:<br>
  <code>SELECT COUNT(*) FROM `customers` WHERE `store_id` = 1;</code></p>
  
  <p><strong>Query #2</strong> takes ~1.5ms:<br>
  <code>SELECT COUNT(*) FROM `customers` WHERE `store_id` = 1 AND `email` IS NULL;</code></p>
  
  <p><strong>Query #3</strong> takes a whopping <strong>5 seconds</strong>:<br>
  <code>SELECT COUNT(*) FROM `customers` WHERE `store_id` = 1 AND `email` IS NOT NULL;</code></p>
  
  <p>Notes:</p>
  
  <ul>
  <li>I've simplified the table to ask the question, but the query is identical.</li>
  <li>Yes, my table is optimized.</li>
  <li>Yes, both fields are indexed, see the create syntax above.</li>
  <li>There are only a few <code>store_id</code>s, but every record has one.</li>
  <li>There are very few customers with <code>email</code> set to <code>null</code>.</li>
  </ul>
  
  <p>I find a few things strange here:</p>
  
  <ol>
  <li>Query #1 is simplest! There are only a few possible INT values. Shouldn't it be fastest?</li>
  <li>Why is Query #3 so slow? I could cut the time in half by doing the other two queries, and subtracting #1 from #2, but I shouldn't have to.</li>
  </ol>
  
  <p>Any thoughts on this seemingly basic question? Feel like I'm missing something simple. Did I sleep through a class in db school?</p>