title: Speed up this MySQL query
body: <p>I have a query which is getting slower and slower because there are more and more records in my table. So I'm trying to speed things up.</p>
  
  <p>Database size:<br>
  Records: 1,200,000<br>
  Data    22,9    MiB<br>
  Index   46,8    MiB<br>
  Total   69,7    MiB<br></p>
  
  <p>The purpose of the query is counting the number of records that exist that match the conditions. The conditions are a date (current date) and a status number. See query below:</p>
  
  <pre><code>SELECT
    COUNT(id) AS total
  FROM
    order_process
  WHERE
    DATE(datetime) = CURDATE() AND
    status = '7';
  </code></pre>
  
  <p>At the moment, this query is taking 800ms. And I need to run this query multiple times with different dates. These are all in the same script so script execution is going over the 3 seconds at the moment. How can I speed this up?</p>
  
  <p>What have I already done:</p>
  
  <ul>
  <li>Created indexes (Index on <code>status</code> and <code>datetime</code> both don't speed up the query).</li>
  <li>Tested InnoDB engine (which is slower, mostly reading on this table)</li>
  </ul>
  
  <p>To make it complete, below the current table setup.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `order_process` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `order_id` int(11) NOT NULL,
    `status` int(11) NOT NULL,
    `datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `remark` text NOT NULL,
    PRIMARY KEY (`id`),
    KEY `orderid` (`order_id`),
    KEY `datetime` (`datetime`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
  </code></pre>