CODEWITHSUNDEEP
mysqlindexinggroup-byinnodb
Pavel Manylov
Pavel Manylov

Reputation: 649

mysql - group by indexed columns + where by indexed column caused speed decrease

I have table statistics with next structure:

+-------------------+----------------------+------+-----+---------+----------------+
| 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    |                |
+-------------------+----------------------+------+-----+---------+----------------+

With keys on created_at, year_in_tz, month_in_tz and on (year_in_tz, month_in_tz):

 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);

Some queries example...

mysql> 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> 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> 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> 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)

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 faster?

P.S. 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:

alter table lending_statistics add index created_at_with_year_and_month_in_tz (created_at,year_in_tz,month_in_tz);

mysql> 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 |
+----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+

1 row in set (0.05 sec)

mysql> 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)

Upvotes: 1

Views: 635

Answers (3)

KKK
KKK

Reputation: 1085

Add the field ID to your index created_at_with_year_and_month_in_tz and then change your select statement to use

select count(id) ....

In MySQL 5.6 the ICP feature might help in this case cause all fields accessed are part of the index. I believe that MySQL might reads the actual data record when you specify count(*) hence it needs to read the index file as well as the datafile.

Upvotes: 1

user279923
user279923

Reputation:

Slow COUNT(*) queries is the often trouble of MySQL & PostgreSQL (and other RDBMS), because sequental table scan is performed during the query execution. Try to think about caching your aggregated data somewhere else: memcached, redis, etc.

Upvotes: 0

gbn
gbn

Reputation: 432261

Try this, there is a known MySQL issue with datetime indexes

    WHERE
        created_at BETWEEN 
               CAST('2011-10-31 20:00:00' AS datetime) AND 
               CAST('2011-12-31 19:59:59'  AS datetime)

Upvotes: 0

Related Questions