Bug #55204 MySQL 5.5.4 has different execution plan
Submitted: 13 Jul 2010 7:07 Modified: 17 Aug 2010 9:41
Reporter: Vadim TKACHENKO Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5.4 OS:Any
Assigned to: Evgeny Potemkin
Triage: Triaged: D2 (Serious)

[13 Jul 2010 7:07] Vadim TKACHENKO
Description:
For table
CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100000001 DEFAULT CHARSET=latin1

with 100000000 records

MySQL 5.5.4 shows different execution plan then MySQL 5.1.48 with InnoDB-plugin.

For 5.1:
explain select avg(id) from sbtest;
+----+-------------+--------+-------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | sbtest | index | NULL          | k    | 4       | NULL | 100000081 | Using index | 
+----+-------------+--------+-------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

For 5.5.4:
explain select avg(id) from sbtest;
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows      | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+-------------+
|  1 | SIMPLE      | sbtest | index | NULL          | PRIMARY | 4       | NULL | 100000081 | Using index | 
+----+-------------+--------+-------+---------------+---------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

On cold the second execution plan is 2x slower.
in 5.1:
30 sec
in 5.5.4:
1 min 1 sec

How to repeat:
create table
CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100000001 DEFAULT CHARSET=latin1

populate with
sysbench --test=oltp --oltp-table-size=100000000 prepare

check EXPLAIN
[13 Jul 2010 20:44] Sveta Smirnova
Thank you for the report.

Verified as described.
[13 Jul 2010 20:55] Sveta Smirnova
MyISAM storage engine affected as well.
[17 Aug 2010 9:41] Evgeny Potemkin
Fixed by the fix for bug#39653.