title: How can i speed up a group by query that already uses indexes?
body: <p>We have a MyISAM table with approximately 75 milion rows that has 5 columns: </p>
  
  <pre><code>id (int), 
  user_id(int), 
  page_id (int), 
  type (enum with 6 strings)
  date_created(datetime).
  </code></pre>
  
  <p>We have a primary index on the ID column, a unique index (user_id, page_id, date_created) AND a composite index (page_id, date_created)</p>
  
  <p>The problem is that the query below takes up to 90 seconds to complete</p>
  
  <pre><code>SELECT SQL_NO_CACHE user_id, count(id) nr 
  FROM `table` 
  WHERE `page_id`=301 
  and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' 
  AND page_id&lt;&gt;user_id 
  group by `user_id`
  </code></pre>
  
  <p>This is the explain of this query</p>
  
  <pre><code>+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
  | id | select_type | table                      | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                        |
  +----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
  |  1 | SIMPLE      | table                      | range | page_id       | page_id | 12      | NULL | 520024 | Using where; Using temporary; Using filesort |
  +----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
  </code></pre>
  
  <p><strong>EDIT:</strong>
  At the suggestion of ypercube I tried adding a new index (page_id, user_id, date_created). However mysql does not use it bu default so i had to suggest it to the query optimizer. Here is the new query and the explain:</p>
  
  <pre><code>SELECT SQL_NO_CACHE user_id, count(*) nr FROM `table` USE INDEX (usridexp) WHERE `page_id`=301 and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' AND page_id&lt;&gt;user_id group by `user_id` ORDER BY NULL
  
  
      +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
      | id | select_type | table                      | type | possible_keys | key      | key_len | ref   | rows    | Extra                    |
      +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
      |  1 | SIMPLE      | table                      | ref  | usridexp      | usridexp | 4       | const | 3943444 | Using where; Using index |
      +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
  </code></pre>