October 24, 2014

MySQL 5.5 and MySQL 5.6 default variable values differences

As the part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query:

Lets go over to see what are the most important changes […]

How to convert MySQL’s SHOW PROFILES into a real profile

SHOW PROFILES shows how much time MySQL spends in various phases of query execution, but it isn’t a full-featured profile. By that, I mean that it doesn’t show similar phases aggregated together, doesn’t sort them by worst-first, and doesn’t show the relative amount of time consumed. I’ll profile the “nicer_but_slower_film_list” included with the Sakila sample […]

Possible optimization for sort_merge and UNION ORDER BY LIMIT

Every so often you need to perform sort results retrieved from MySQL when your WHERE clause goes beyound col=const values which would allow MySQL to still use second portion of the index for the order by. Ranges as well as IN lists make this optimization impossible, not even speaking about index merge optimization. Lets look […]