Hello guys
my.cnf is the same for 5.5 and 5.6.
One of the biggest slowdowns is IP2Location, we use DB14 version:
http://ip2location.com/databases/db14-ip-country-region-city-latitude-longitude-zipcode-timezone-isp-domain-netspeed
The page also shows the table layout.
Feel free to buy it and test it :)
Our ip2location table:
Data 886.3 MiB
Index 115.9 MiB
Key Type Unique Packed Column Cardinality
ipeb BTREE No No ip_end 8498157
ip_begin 8498157
Example query:
SELECT *
FROM `configuration`.`ip2location`
WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676
LIMIT 1;
Duration in 5.6: 1 row in set (1.34 sec)
EXPLAIN output MySQL 5.6:
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+
| 1 | SIMPLE | ip2location | range | ipeb | ipeb | 4 | NULL | 8131911 | Using index condition; Using MRR |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+
Duration in 5.5: 1 row in set (0.03 sec)
EXPLAIN output MySQL 5.5:
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | ip2location | range | ipeb | ipeb | 4 | NULL | 8131911 | Using where |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+
The weird thing is: looking at the EXPLAIN output 5.6 should be faster?