title: mysql - group by indexed columns + where by indexed column caused speed decrease
body: <p>I have table <code>statistics</code> with next structure:</p>
  
  <pre><code>+-------------------+----------------------+------+-----+---------+----------------+
  | Field             | Type                 | Null | Key | Default | Extra          |
  +-------------------+----------------------+------+-----+---------+----------------+
  | id                | int(11)              | NO   | PRI | NULL    | auto_increment |
  | created_at        | datetime             | YES  | MUL | NULL    |                |
  | year_in_tz        | smallint(5) unsigned | YES  | MUL | NULL    |                |
  | month_in_tz       | tinyint(3) unsigned  | YES  | MUL | NULL    |                |
  +-------------------+----------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>With keys on created_at, year_in_tz, month_in_tz and on (year_in_tz, month_in_tz):</p>
  
  <pre><code> ALTER TABLE `statistics` ADD INDEX created_at (created_at);
   alter table statistics add index year_in_tz (year_in_tz);
   alter table statistics add index month_in_tz (month_in_tz);
   alter table statistics add index year_month_in_tz(year_in_tz,month_in_tz);
  </code></pre>
  
  <p>Some queries example...</p>
  
  <pre><code>mysql&gt; SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz 
         FROM `statistics` 
         GROUP BY year_in_tz, month_in_tz;
  +-----------+------------+-------------+
  | count_all | year_in_tz | month_in_tz |
  +-----------+------------+-------------+
  |    467890 |       2011 |          11 |
  |   7339389 |       2011 |          12 |
  +-----------+------------+-------------+
  2 rows in set (5.04 sec)  
  
  mysql&gt; describe SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz FROM `statistics` GROUP BY year_in_tz, month_in_tz;
   +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
   | id | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows    | Extra       |
   +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
   |  1 | SIMPLE      | statistics         | index | NULL          | year_month_in_tz | 5       | NULL | 7797984 | Using index |
   +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
   1 row in set (0.01 sec)
  
   mysql&gt; SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz 
          FROM `statistics` 
          WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59') 
          GROUP BY year_in_tz, month_in_tz;
   +-----------+------------+-------------+
   | count_all | year_in_tz | month_in_tz |
   +-----------+------------+-------------+
   |    467890 |       2011 |          11 |
   |   7339389 |       2011 |          12 |
   +-----------+------------+-------------+
   2 rows in set (1 min 33.46 sec)
  
   mysql&gt; describe SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz FROM `statistics` WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59') GROUP BY year_in_tz, month_in_tz;
   +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
   | id | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows    | Extra       |
   +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
   |  1 | SIMPLE      | statistics         | index | created_at    | year_month_in_tz | 5       | NULL | 7797984 | Using where |
   +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
   1 row in set (0.07 sec)
  </code></pre>
  
  <p>So if I use where statement with clause on indexed column + group by indexed columns, speed is extremely low. 
  Maybe someone know how to improve last query to make it <em>faster</em>?</p>
  
  <p><strong>P.S.</strong> After playing with indexes, I found that new index on (created_at, year_in_tz, month_in_tz) made query run faster, but I want 0-1 seconds per query, not 10 seconds:</p>
  
  <pre><code>alter table lending_statistics add index created_at_with_year_and_month_in_tz (created_at,year_in_tz,month_in_tz);
  
  mysql&gt; describe SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz FROM `statistics`        WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59') GROUP BY year_in_tz, month_in_tz;
  +----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+
  | id | select_type | table              | type  | possible_keys                                   | key                                  | key_len | ref  | rows    | Extra                                                     |
  +----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+
  |  1 | SIMPLE      | statistics         | range | created_at,created_at_with_year_and_month_in_tz | created_at_with_year_and_month_in_tz | 9       | NULL | 3612208 | Using where; Using index; Using temporary; Using filesort |
  +----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+
  </code></pre>
  
  <p>1 row in set (0.05 sec)</p>
  
  <pre><code>mysql&gt; SELECT COUNT(*) AS count_all,        year_in_tz, month_in_tz        FROM `lending_statistics`        WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59')        GROUP BY year_in_tz, month_in_tz;
  +-----------+------------+-------------+
  | count_all | year_in_tz | month_in_tz |
  +-----------+------------+-------------+
  |    467890 |       2011 |          11 |
  |   7339389 |       2011 |          12 |
  +-----------+------------+-------------+
  2 rows in set (10.62 sec)
  </code></pre>