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 CPU Architecture:Any

[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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.