Re: Mysql 5.5 and 5.6 slower than 5.0 and 5.1 on selected query
I have reproduced it on signle table.
Same server, same configuration. Linux 64 bit.
Both innodb, on both databases innodb_buffer_pool_size bigger than all db size. Main data stored on SSD, all temporary tables created to RAM disk.
Query:
create temporary table norepl_t1 engine=myisam select amount, price, money, id_product from scj_tt_ships where scj_ships_id between 200000 and 400000;
Mysql 5.6.11 = 23 sec;
Mysql 5.1.67 = 15 sec;
If we will change query just to SUM first column:
select sum(amount) from scj_tt_ships where scj_ships_id between 200000 and 400000;
Mysql 5.6.11 = 11 sec;
Mysql 5.1.67 = 10 sec;
scj_tt_ships - any dummy big innodb table.
Here is create table
CREATE TABLE `scj_tt_ships` (
`scj_ships_id` int(11) NOT NULL,
`id_product` smallint(5) unsigned NOT NULL,
`amount` decimal(12,6) NOT NULL DEFAULT '0.000000',
`price` decimal(12,6) NOT NULL DEFAULT '0.000000',
`id_client_tt` int(11) NOT NULL,
`id_ta` mediumint(8) unsigned NOT NULL DEFAULT '0',
`money` decimal(16,6) NOT NULL DEFAULT '0.000000',
KEY `scj_ships_id` (`scj_ships_id`),
KEY `id_client_tt` (`id_client_tt`),
KEY `id_product` (`id_product`)
) ENGINE=InnoDB;
Here uis
5.1
mysql> show table status where name='scj_tt_ships' \G
*************************** 1. row ***************************
Name: scj_tt_ships
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 32983641
Avg_row_length: 64
Data_length: 2123366400
Max_data_length: 0
Index_length: 2020605952
Data_free: 7340032
Auto_increment: NULL
Create_time: 2013-03-21 11:55:52
Update_time: NULL
Check_time: NULL
Collation: cp1251_ukrainian_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)
mysql> SHOW VARIABLES LIKE '%buffer%' ;
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 38654705664 |
| innodb_log_buffer_size | 67108864 |
| join_buffer_size | 33554432 |
| key_buffer_size | 134217728 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 33554432 |
| read_rnd_buffer_size | 16777216 |
| sort_buffer_size | 67108864 |
| sql_buffer_result | OFF |
+-------------------------+-------------+
5.6
mysql> show table status where name='scj_tt_ships' \G
*************************** 1. row ***************************
Name: scj_tt_ships
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 31397706
Avg_row_length: 63
Data_length: 2009071616
Max_data_length: 0
Index_length: 1949302784
Data_free: 6291456
Auto_increment: NULL
Create_time: 2013-05-11 02:37:35
Update_time: NULL
Check_time: NULL
Collation: cp1251_ukrainian_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%buffer%' ;
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 8589934592 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 33554432 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 33554432 |
| read_rnd_buffer_size | 16777216 |
| sort_buffer_size | 67108864 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+