MySQL version 4.1 was quite revolutionary. The main reason for that was support for sub-queries.1
However since then MySQL users were rather discouraged to use that functionality, basically due to the implementation’s poor performance and forced to build complicated queries based on joins rather than on subqueries.
Of course you can do some effort to optimize your subquery with sometimes very good results2. Not always it’s easy or even possible if you can’t change the code though.
You’d say it’s not a problem for typical OLTP, web based traffic at all, just don’t use subqueries! That’s true, for most of the times anyway.
In our case we are sentenced to deal with subqueries beacuse of third party MediaWiki extension called DynamicPageList, which tends to generate ones, and our users want to use.
So, let’s check with some live examples, produced by DynamicPageList extension, if poor subqueries situation has changed for MySQL over the years. And my inspiration for that comes from the fact that MariaDB 5.3.x line has became GA recently and one of it’s most important features is subquery optimization .
I’ve chosen two example nasty queries that I found in slow log and compared their execution times on different MySQL versions. First query has one sub-select and many joins, second one has two sub-selects.
Thing I need to mention is the optimizer_switch variable, which is much more complex in MariaDB than in stock MySQL or Percona. And this variable has the subquery_cache switch, which is crucial in this test.
Details on both the optimizer_switch and the tested queries to be found below the test results.
Remember! These are only two very specific queries using sub-query feature, results for other type of queries and workload may be completely different for you, so please don’t base your MySQL variation decision on these tests!
Test results
| Query 1 cold | Query 1 warm | Query 2 cold | Query 2 warm | ||
| MariaDB 5.3.5 | 11.78 | 10.82 | 13.03 | 9.78 | |
| MariaDB 5.3.5 subquery cache | 1.66 | 0.87 | 5.53 | 2.4 | |
| MariaDB 5.5.21 | 13.79 | 13.16 | 13.76 | 10.96 | |
| MariaDB 5.5.21 subquery cache | 1.76 | 0.92 | 5.06 | 2.45 | |
| Percona 5.1.61 | 11.04 | 10.05 | 12.52 | 9.89 | |
| Percona 5.5.21 | 13.54 | 12.01 | 13.69 | 10.98 | |
| MySQL 5.5.22 | 11.36 | 10.24 | 12.9 | 10.23 | |
| MySQL 5.6.4-m7 | 13.14 | 12.06 | 14.26 | 11.78 | |