title: Optimising a MySQL Query that takes ~30 seconds to run
body: <p>I am running a query to get the total notes input by each users between a date range. This is the query I am running:</p>
  
  <pre><code>SELECT SQL_NO_CACHE 
      COUNT(notes.user_id) AS "Number of Notes"
  
  FROM csu_users
  
  JOIN notes      ON notes.user_id    = csu_users.user_id
  
  WHERE notes.timestamp BETWEEN "2013-01-01" AND "2013-01-31"
  AND notes.system = 0
  
  GROUP BY csu_users.user_id
  </code></pre>
  
  <p>Some notes about my setup:</p>
  
  <ul>
  <li>The query takes between 30 and 35 seconds to run, which is too long for our system</li>
  <li>This is an InnoDB table</li>
  <li>The <code>notes</code> table is about 1GB, with ~3,000,000 rows</li>
  <li>I'm deliberately using <code>SQL_NO_CACHE</code> to ensure an accurate benchmark</li>
  </ul>
  
  <p>The output of <code>EXPLAIN SELECT</code> is as follows (I've tried my best to format it):</p>
  
  <pre><code>id  select_type table       type    possible_keys             key       key_len ref                           rows  Extra
  1   SIMPLE      csu_users   index   user_id                   user_id   5       NULL                          1     Using index
  1   SIMPLE      notes       ref     user_id,timestamp,system  user_id   4       REFSYS_DEV.csu_users.user_id  152   Using where
  </code></pre>
  
  <p>I have the following indexes applied:</p>
  
  <p><strong><code>notes</code></strong></p>
  
  <ul>
  <li>Primary Key - <code>id</code></li>
  <li><code>item_id</code></li>
  <li><code>user_id</code></li>
  <li><code>timestamp</code> <em>(note: this is actually a <code>DATETIME</code>. The name is just misleading, sorry!)</em></li>
  <li><code>system</code></li>
  </ul>
  
  <p><strong><code>csu_users</code></strong></p>
  
  <ul>
  <li>Primary Key - <code>id</code></li>
  <li><code>user_id</code></li>
  </ul>
  
  <p><strong>Any ideas how I can speed this up? Thank you!</strong></p>