No problem Dimitri :)
Here we go. lots of text incoming...
This whole post is always here as txt file for better readability:
http://www.livingliquid.com/at/images/mysql/mysql55-56_profiling_20130329.txt
LIMIT-1 series:
Query: SELECT * FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 LIMIT 1;
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 |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+-------------+
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| 1 | 0.02444125 | SELECT * FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 LIMIT 1 |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000066 |
| checking permissions | 0.000008 |
| Opening tables | 0.000019 |
| System lock | 0.000011 |
| init | 0.000078 |
| optimizing | 0.000014 |
| statistics | 0.000071 |
| preparing | 0.000024 |
| executing | 0.000004 |
| Sending data | 0.024106 |
| end | 0.000009 |
| query end | 0.000003 |
| closing tables | 0.000006 |
| freeing items | 0.000018 |
| logging slow query | 0.000003 |
| cleaning up | 0.000003 |
+----------------------+----------+
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 |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+----------------------------------+
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
| 1 | 1.37092625 | SELECT * FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 LIMIT 1 |
+----------+------------+-------------------------------------------------------------------------------------------------------------+
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000047 |
| checking permissions | 0.000008 |
| Opening tables | 0.000013 |
| init | 0.000033 |
| System lock | 0.000010 |
| optimizing | 0.000010 |
| statistics | 0.000132 |
| preparing | 0.000020 |
| executing | 0.000005 |
| Sending data | 1.370303 |
| end | 0.000020 |
| query end | 0.000005 |
| closing tables | 0.000020 |
| freeing items | 0.000260 |
| cleaning up | 0.000043 |
+----------------------+----------+
COUNT-STAR series:
Query: SELECT count(*) FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676;
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; Using index |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+
+----------+------------+------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------+
| 1 | 1.94352700 | SELECT count(*) FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 |
+----------+------------+------------------------------------------------------------------------------------------------------------+
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000101 |
| checking permissions | 0.000009 |
| Opening tables | 0.000121 |
| System lock | 0.000011 |
| init | 0.000030 |
| optimizing | 0.000021 |
| statistics | 0.000074 |
| preparing | 0.000018 |
| executing | 0.000005 |
| Sending data | 1.942431 |
| end | 0.000023 |
| query end | 0.000004 |
| closing tables | 0.000009 |
| freeing items | 0.000661 |
| logging slow query | 0.000007 |
| cleaning up | 0.000004 |
+----------------------+----------+
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 where; Using index |
+----+-------------+-------------+-------+---------------+------+---------+------+---------+--------------------------+
+----------+------------+------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------+
| 1 | 3.23581200 | SELECT count(*) FROM `configuration`.`ip2location` WHERE `ip_end` >= 418487676 AND `ip_begin` <= 418487676 |
+----------+------------+------------------------------------------------------------------------------------------------------------+
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000041 |
| checking permissions | 0.000007 |
| Opening tables | 0.000012 |
| init | 0.000025 |
| System lock | 0.000038 |
| optimizing | 0.000015 |
| statistics | 0.040483 |
| preparing | 0.000041 |
| executing | 0.000005 |
| Sending data | 3.194836 |
| end | 0.000020 |
| query end | 0.000005 |
| closing tables | 0.000011 |
| freeing items | 0.000219 |
| cleaning up | 0.000056 |
+----------------------+----------+
OPTIMIZER trace output in MySQL 5.6:
SET OPTIMIZER_TRACE="enabled=on,end_marker=on";
ERROR 1231 (42000): Variable 'optimizer_trace' can't be set to the value of 'end_marker=on'
Mmmhhh... strange. I use 5.6.10 Linux - Generic (glibc 2.5) (x86, 64-bit)
mysql> show variables like '%trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=on,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 1000000 |
| optimizer_trace_offset | -1 |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)
No 'end_marker=' available. Is this ok?
I've read the manual and end_marker seems to be optional and helps 'reading' the output, so i skipped it.
ANALYZE TABLE `ip2location` doesn't change anything.
OPTIMIZE TABLE `ip2location` too.
Both trace outputs:
http://www.livingliquid.com/at/images/mysql/mysql_56_ip2location_countstar.trace.out
http://www.livingliquid.com/at/images/mysql/mysql_56_ip2location_limit1.trace.out
Can i compile MySQL 5.5 to support tracing?
Edited 2 time(s). Last edit at 03/29/2013 02:23AM by Christian Meisinger.