StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql - group by indexed columns + where by indexed column caused speed decrease
text
Body
copied!<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> 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) </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> 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> 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload