MySQL SELECT COUNT with GROUP and ORDER performance issue

Question

The Facts:

Dedicated Server, 4 Cores, 16GB
MySQL 5.5.29-0ubuntu0.12.10.1-log - (Ubuntu)
One Table, 1.9M rows and growing

I need all sorted rows for export or a 5er chunk. The query takes 25 seconds with Copying To Tmp Table 23.3 s

I tried InnoDB and MyISAM, changing the index order, using a MD5 Hash of some_text as GROUP BY, partition the table by day.

dayis a Unix-Timestamp and alway present. lang some_bool some_filter ano_filter rel_id could be in where clause but not need to.

Here is the MyISAM example:

The table

mysql> SHOW CREATE TABLE data \G;
*************************** 1. row ***************************
       Table: data
Create Table: CREATE TABLE `data` (
  `data_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rel_id` int(11) NOT NULL,
  `some_text` varchar(255) DEFAULT NULL,
  `lang` varchar(3) DEFAULT NULL,
  `some_bool` tinyint(1) DEFAULT NULL,
  `some_filter` varchar(40) DEFAULT NULL,
  `ano_filter` varchar(10) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  PRIMARY KEY (`data_id`),
  KEY `cnt_idx` (`some_filter`,`ano_filter`,`rel_id`,`lang`,`some_bool`,`some_text`,`day`)
) ENGINE=MyISAM AUTO_INCREMENT=1900099 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

The query

mysql> EXPLAIN SELECT `some_text` , COUNT(*) AS `num` FROM `data` 
 WHERE `lang` = 'en' AND `day` BETWEEN '1364342400' AND
 '1366934399' GROUP BY `some_text` ORDER BY `num` DESC \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: data
         type: index
possible_keys: NULL
          key: cnt_idx
      key_len: 947
          ref: NULL
         rows: 1900098
        Extra: Using where; Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> SELECT `some_text` , COUNT(*) AS `num` FROM `data` 
 WHERE `lang` = 'en' AND `day` BETWEEN '1364342400' AND '1366934399' 
 GROUP BY `some_text` ORDER BY `num` DESC LIMIT 5 \G;
...
*************************** 5. row ***************************
5 rows in set (24.26 sec)

Any idea how to speed up that thing?`

Kermit Kermit · Accepted Answer · 2013-04-26T21:49:37

No index is being used because of the column order in the index. Indexes work left to right. For this query to use an index, you would need an index of lang, day.

MySQL SELECT COUNT with GROUP and ORDER performance issue

1 Answers