title: Best way to count millions of rows between certain dates in MySQL
body: <p>Here is my SQL for creating a table:</p>
  
  <pre><code>$sql_create_table = "CREATE TABLE {$table_name} (
                    hit_id bigint(20) unsigned NOT NULL auto_increment,
                    user_id int(7) unsigned NOT NULL default '0',
                    optin_id int(8) unsigned NOT NULL default '0',
                    hit_date datetime NOT NULL default '0000-00-00 00:00:00',
                    hit_type varchar(10) NOT NULL default '',
                    PRIMARY KEY  (hit_id),
                    KEY user_id (user_id)
               ) $charset_collate; ";
  </code></pre>
  
  <p>I need to know the fastest way to count the number of rows within a query. My current query doesn't cut it for going through millions of rows.</p>
  
  <pre><code>$sql = "SELECT hit_id FROM $table_name WHERE user_id = %d AND hit_type = %s AND hit_date &gt;= FROM_UNIXTIME(%d) AND hit_date &lt;= FROM_UNIXTIME(%d)";
  </code></pre>
  
  <p>I've tried this with no luck (not returning the proper results):</p>
  
  <pre><code>$sql = "SELECT COUNT(*) FROM $table_name WHERE user_id = %d AND hit_type = %s AND hit_date &gt;= FROM_UNIXTIME(%d) AND hit_date &lt;= FROM_UNIXTIME(%d)";
  </code></pre>
  
  <p>What do I need to do to make this query efficient so that it doesnt time out for millions of rows? I simply want to count the number of rows within the specified parameter set.</p>