Description:
Older versions of MySQL (e.g., 5.0.67) give twice as better performance than the latest versions (e.g., 5.6.13).
When I perform the following five steps with older versions of MySQL (e.g., MySQL 5.0.67) it completes all of these steps in approximately 46 minutes.
However, MySQL 5.6.13 performs the same tasks in approximately 98 minutes (almost double time).
Step 1:
=======
Create the following 'source' table:
CREATE TABLE `source` (
`CustomerID` mediumint(9) NOT NULL,
`ProductID` varchar(255) NOT NULL,
`OrderID` bigint(20) NOT NULL,
`PurchaseCode` smallint(6) NOT NULL,
KEY `CustomerID` (`CustomerID`,`OrderID`))
ENGINE=MyISAM DEFAULT CHARSET=latin1;
Step 2:
=======
Insert 2000000 values into this 'source' table with the following pattern:
INSERT INTO `source` (`CustomerID`, `ProductID`, `OrderID`, `PurchaseCode`) VALUES
(1, 'a', 1, 100),
(2, 'b', 2, 100),
(3, 'a', 3, 100),
(4, 'b', 4, 100),
(5, 'a', 5, 100),
(6, 'b', 6, 100),
(7, 'a', 7, 100),
(8, 'b', 8, 100),
(9, 'a', 9, 100),
(10, 'b', 10, 100),
(11, 'a', 11, 100),
(12, 'b', 12, 100),
(13, 'a', 13, 100),
(14, 'b', 14, 100),
(15, 'a', 15, 100),
(16, 'b', 16, 100),
(17, 'a', 17, 100),
(18, 'b', 18, 100),
(19, 'a', 19, 100),
(20, 'b', 20, 100),
(1, 'a', 21, 100),
(2, 'b', 22, 100),
(3, 'a', 23, 100),
(4, 'b', 24, 100),
(5, 'a', 25, 100),
(6, 'b', 26, 100),
(7, 'a', 27, 100),
(8, 'b', 28, 100),
(9, 'a', 29, 100),
(10, 'b', 30, 100),
...
...
(20, 'b', 2000000, 100);
Step 3:
=======
Create a 'destination' table like this:
CREATE TABLE `destination` (
`CustomerID` mediumint(9) NOT NULL default '0',
`ProductID` varchar(255) NOT NULL default '',
`OrderID` bigint(20) NOT NULL,
`PurchaseCode` smallint(6) NOT NULL default '0',
KEY `CustomerID` (`CustomerID`),
KEY `OrderID` (`OrderID`) )
ENGINE=MyISAM DEFAULT CHARSET=latin1;
and then disable the keys of this table using the following query:
ALTER TABLE `destination` DISABLE KEYS;
Step 4:
=======
Copy some data from 'source' table to 'destination' table using the following query:
INSERT INTO `destination` SELECT * FROM `source` WHERE
`CustomerID` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16)
AND (`OrderID` >= 100 AND `OrderID`<= 1000000);
Step 5:
=======
Enable the keys again using the following query:
ALTER TABLE `destinationTable` ENABLE KEYS;
NOTE:
=====
If I reduce the number of filter values in step 4 then the performance of MySQL 5.0.67 and MySQL 5.6.13 are almost equal.
INSERT INTO `destination` SELECT * FROM `source` WHERE
`CustomerID` IN (1, 2, 3, 4, 5, 6)
AND (`OrderID` >= 100 AND `OrderID`<= 1000000);
Note:
=====
Disabling the performance schema also does not help.
How to repeat:
#Using the following parameters in my.ini file:
[client]
port=3306
[mysqld]
max_delayed_threads=8
max_tmp_tables=1024
tmpdir="D:/Temp"
bulk_insert_buffer_size=16M
max_allowed_packet=256M
console=1
port=3306
basedir="C:/Program Files (x86)/MySQL/"
datadir="C:/Program Files (x86)/MySQL/Data"
default-storage-engine=MYISAM
max_connections=600
query_cache_size=0
table_open_cache=1004
tmp_table_size=16M
thread_cache_size=20
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=17M
key_buffer_size=256M
read_buffer_size=64K
read_rnd_buffer_size=2M
sort_buffer_size=16M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=17M
innodb_log_file_size=10M
innodb_thread_concurrency=8
sql-mode="NO_ENGINE_SUBSTITUTION"
open-files-limit=2048
delayed_insert_limit=1000
delayed_insert_timeout=300
delayed_queue_size=16M
