Search Results for: select order by

When the subselect runs faster

A few weeks ago, we had a query optimization request from one of our customer. The query was very simple like:

This column in the table is looks like this:

The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as […]

Faster MySQL failover with SELECT mirroring

One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER […]

MySQL Performance – eliminating ORDER BY function

One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can […]

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 […]

Using index for ORDER BY vs restricting number of rows.

One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which sell goods, goods may be from different categories, different sellers different locations which can be filtered on, and there […]

Looking deeper into InnoDB’s problem with many row versions

A few days ago I wrote about MySQL performance implications of InnoDB isolation modes and I touched briefly upon the bizarre performance regression I found with InnoDB handling a large amount of versions for a single row. Today I wanted to look a bit deeper into the problem, which I also filed as a bug. First […]

Percona Server 5.6.22-71.0 is now available

Percona is glad to announce the release of Percona Server 5.6.22-71.0 on January 12, 2015. Download the latest version from the Percona web site or from the Percona Software Repositories. Based on MySQL 5.6.22, including all the bug fixes in it, Percona Server 5.6.22-71.0 is the current GA release in the Percona Server 5.6 series. Percona Server is open-source […]