Execution plan: wrong index selected, very poor performance.
Hi,
In short, wrong index is being used on large table, resulting in extremely poor performance while it selects proper key on another server with a bit smaller table for a huge performance benefit.
A little bit of info (obfuscated table/column/index names) and stuff I've tried to do to have it select the right index.
MySQL 5.1.57 instance.
Table:
CREATE TABLE `t1` (
`c1` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',
`c2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c6` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c7` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c8` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`c9` int(11) NOT NULL,
`c10` int(11) NOT NULL,
`c11` int(11) NOT NULL,
`c12` int(11) NOT NULL,
`c13` int(11) NOT NULL,
`c14` int(11) NOT NULL,
`c15` int(11) NOT NULL,
`c16` int(11) NOT NULL,
KEY `key_c2` (`c2`),
KEY `key_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Production server results:
mysql> EXPLAIN SELECT c2 c2, SUM(c10) c10 FROM t1 WHERE c1 >= DATE_SUB('2012-06-01 00:00:00', INTERVAL 0 SECOND) AND c1 < DATE_SUB('2012-07-01 00:00:00', INTERVAL 0 SECOND) GROUP BY c2 ORDER BY c2;
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
| 1 | SIMPLE | t1 | index | key_c1 | key_c2 | 767 | NULL | 57169660 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.15 sec)
mysql> show table status like 't1';
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Compact | 56947209 | 102 | 5837422592 | 0 | 2481979392 | 5242880 | NULL | 2012-07-18 11:14:20 | NULL | NULL | utf8_unicode_ci | NULL | | |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (1.07 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1 | 1 | key_c2 | 1 | c2 | A | 16139 | NULL | NULL | | BTREE | |
| t1 | 1 | key_c1 | 1 | c1 | A | 103223 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
As you can see, without using FORCE INDEX (key_c1), it will use inefficient key_c2 instead of key_c1, resulting in query executing way too long. Why's it using key_c2 instead of key_c1?
Funny thing is. I have a bit older copy of the same table, with about 49M rows. On that instance, proper key is selected.
Staging server results:
mysql-staging> EXPLAIN SELECT c2 c2, SUM(c10) c10 FROM t1 WHERE c1 >= DATE_SUB('2012-06-01 00:00:00', INTERVAL 0 SECOND) AND c1 < DATE_SUB('2012-07-01 00:00:00', INTERVAL 0 SECOND) GROUP BY c2 ORDER BY c2;
+----+-------------+-------+-------+---------------+--------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | t1 | range | key_c1 | key_c1 | 4 | NULL | 6187104 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)
mysql-staging> show table status like 't1';
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| t1 | InnoDB | 10 | Compact | 49907150 | 103 | 5155848192 | 0 | 2133803008 | 6291456 | NULL | 2011-10-20 11:14:07 | NULL | NULL | utf8_unicode_ci | NULL | | |
+-------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.62 sec)
mysql-staging> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1 | 1 | key_c2 | 1 | c2 | A | 17 | NULL | NULL | | BTREE | |
| t1 | 1 | key_c1 | 1 | c1 | A | 28203 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
I've tried:
mysql> analyze table t1;
mysql> check table t1;
mysql> optimize table t1;
mysql> alter table t1 engine=InnoDB; -- should alredy be done by optimize table...
mysql> drop index key_c1 on t1; create index key_c1 on t1(c1);
mysql> drop index key_c2; -- only key_c1 now exists, still wont use it
Nothing helped, I'm still getting the wrong index selected.
What else is there to try besides using FORCE INDEX in the query itself?
Edited 3 time(s). Last edit at 07/18/2012 10:34AM by Andrej Andrejevic.