Optimizer news: @@optimizer_switch syntax changes and backport

In short, the news are:

  • @@optimizer_switch support was backported into MySQL 5.1
  • The switch syntax was changed from ‘no_optimization_name’ to ‘optimization_name=on|off|default’.
  • Added switches for index_merge, index_merge_intersection, index_merge_union, and index_merge_sort_union optimizations.

The changes will be available in next releases, that is, MySQL 5.1.34 and 6.0.11.
Now with more details:

New switch names

Until now, the syntax mimicked an enum column or the @@sql_mode variable. One could set the value of @@optimizer_switch to a set of keywords, e.g.

SET @@optimizer_switch='no_semijoin,no_materialization';

Presence of a no_xxx keyword meant that its optimization was disabled, its absence meant it was disabled.

As of the next MySQL 5.1/6.0, @@optimizer_switch value is a set of on/off flags:

mysql> SELECT @@optimizer_switch;
| @@optimizer_switch                                                                        |
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |

the SET statement accepts a list of commands:
SET [GLOBAL|SESSION] optimizer_switch='command,command,...'

where each command is one of

default reset all optimization settings to default
optimization_name=off disable the optimization
optimization_name=on enable the optimization
optimization_name=default set the optimization to its default state

The order of the commands does not matter (‘default’ will be executed first if present), setting the same flag twice within one SET command is not allowed. Flags that are not mentioned keep their current values:

mysql> SELECT @@optimizer_switch;
| @@optimizer_switch                                                                        |
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
1 row in set (0.00 sec)

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@optimizer_switch;
| @@optimizer_switch                                                                          |
| index_merge=on,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=on |
1 row in set (0.00 sec)

The advantages of the new way over the old one are that

  1. It is now possible to turn certain optimization on/off with a single statement (SET optimizer_switch=’malfunctioning_optimization=off’) which will not depend on what other optimizer flags exist and what their values are.
  2. One can easily see what optimizer switches are available in the current server.
  3. In contrast to the grand solution of WL#4046, I could code and push this within a reasonable amount of time.

In addition, the mysqld binary got a –optimizer-switch parameter which allows to set optimizer_switch at server startup or in my.cnf file.

The backport

This is actually the reason for making all these changes. It turns out that the optimizer can make a wrong choice when considering whether to use index_merge optimization. This can happen for both valid (unknown data correlations) and not-so-valid (mismatch between cost model and the reality) reasons. A fix for either of these problems would be too intrusive to put into the GA version (betas and major releases were invented for a reason), and we also just do not have it yet. So, we’ve decided to provide at least some resolution for those for whom index_merge made things worse and introduced the following switches

@@optimizer_switch flags in MySQL 5.1
index_merge turns on/off the all index_merge optimizations
index_merge_union turn on/off individual index_merge algorithms (names as in the documentation)

MySQL 6.0 has the above switches and also subquery optimization switches:

@@optimizer_switch flags in MySQL 6.0
semijoin turns on/off the all semi-join optimizations
materialization turns on/off materialization (including semi-join materialization)
loosescan turns on/off semi-join LooseScan strategy (not to be confused with GROUP BY’s LooseScan)
firstmatch turns on/off semi-join FirstMatch strategy

All future optimizations will be switchable as well. We’ve learned the lesson.

Sun Tech Days St. Petersburg 2009

Sun Tech Days St. Petersburg was on Wednesday-Thursday the last week and we’ve had a MySQL booth there. Unlike the last year, we’re full part of Sun now so managed to get a decent-sized booth, arrange for leaflets, and Kostja gave an overall MySQL talk.

Questions at the booth (in no particular order):

  • When will Connector.NET support LINQ Entity framework? (According to Reggie Burnett: it is currently supported in Connector 6.0/Beta, which is expected to be GA soon)
  • Can Connector.Net be used with Mono? (Yes)
  • Is Workbench available for Linux (Yes)
  • When will MySQL support stored procedures? This question seems to have replaced the infamous “When will MySQL support transactions” question.
  • What is the impact of different transaction isolation levels on performance of InnoDB? (no idea. If you ran some experiments please drop a comment)
  • When will MySQL support LIMIT clause inside subqueries, in particular the
    ... WHERE IN (SELECT ... ORDER BY LIMIT n) form? (we would like to add support for this, but no plans ATM. Request taken.)
  • Does MySQL have any limitations on table/database size or number of records in the table? (I gather people are used to having limitations in free versions of SQL Server or Oracle and expect something like that in MySQL)
  • When will fulltext search support searching for different wordforms (that’s a big deal for searching in Russian texts, as the words get different suffixes depending on which grammatic case they are in)? No plans ATM. Perhaps somebody has developed a fulltext parser plugin somewhere?
  • When will InnoDB get efficient support for COUNT(*)?
  • Are there any plans to make MySQL more efficient when handling big blob columns?

We’ve got several complaints that look like bugs:

  • LEFT JOIN and multi-table DELETE fails to delete records when using foreign keys. We’ve got a test case so I’ve filed this as BUG#44207.
  • There’s something wrong with Connector/Java and timezones. I’m not sure if we’ve managed to repeat the problem on our laptops, we were promised a bug report.
  • Another person complained about a sharp slowdown in join performance when table size(s) exceed 1M rows. According to the reporter, all buffers are adequately-sized, EXPLAIN shows that the query plan is the same. No idea what this could be then, as the SQL layer doesn’t have any hard-coded buffer sizes.

Also there was this Java duke guy:
and we’ve figured that it would be nice to get a MySQL dolphin the next year. And have the developer with biggest number of bugs in his code wear it :-).

More MySQL 6.0 news: next subquery optimizations WL pushed

Three days ago I’ve finally managed to push the code for WL#3985 “Subquery optimization: smart choice between semi-join and materialization” into MySQL 6.0. I missed the clone-off date so it won’t be in the upcoming MySQL 6.0.9 release, the only way to get it before the next 6.0.10 release is from the lp:mysql-server/6.0 bazaar repository.

What’s new in the push

Before WL#3985, 6.0’s subquery optimization had these three deficiencies:

  1. For semi-join (see cheatsheet for definition) subqueries, you had to make a choice between having the optimizer use materialization or all other strategies. The default behavior was not to use materialization, you could only get it by setting a server variable to disable all other strategies.
  2. The choice among other strategies (FirstMatch, DuplicateWeedout, LooseScan) wasn’t very intelligent – roughly speaking, the optimizer would first pick a join order as if there were only inner joins, and then remember that some of them are actually semi-joins and try to find how it can resolve semi-joins with the picked join order.
  3. Materialization only worked in the outer-to-inner fashion, that is, if you got a query like
    select * from people where name in (select owner from aircraft) it would still scan the people and make lookups into a temporary table of aircraft owners. It was not possible to make it scan the temptable of aircraft owners and make lookups into people.

WL#3985 fully addresses #1 and #2, and partially addresses #3. That is, now

  • Semi-join subqueries can use Materialization in an inner-to-outer fashion
  • Join optimizer is aware of existence of semi-joins and makes a fully automatic, cost-based choice between FirstMatch, DuplicateWeedout, LooseScan, inner-to-outer and outer-to-inner variants of Materialization.

This is expected to be a considerable improvement. The most common class of subqueries,
SELECT ... WHERE expr IN (SELECT ... w/o GROUPing/UNIONs/etc) AND ...
is now covered by a reasonably complete set of execution strategies and the optimizer is expected to have the capability to choose a good strategy for every case.

Possible gotchas, and we’re looking for input

I can’t state that the subquery optimizer does have the capability to pick a good plan because we haven’t done much experiments with the subquery cost model yet. We intend to do some benchmarking, but will also very much appreciate any input on how does the subquery optimizer behave for real-world queries. The code should be reasonably stable now – there are only three known problems, all of which are not very popular edge cases:

  • LEFT JOINs. You may get wrong query results when the subquery or parent subquery use left joins.
  • “Grandparent” correlation. A query with a semi-join child subquery which has a semi-join grandchild subquery which refers to a column in the top-level select may produce wrong query plans/results under certain circumstances.
  • Different datatypes. You may get wrong query results of queries that have col1 IN (SELECT col2) where col1 and col2 are of different types (which should not happen too often in practice)

If you have subqueries with LEFT JOINs, please let us know also, because so far all LEFT JOIN+subquery cases we have were generated by the random query generator, certain properties of MySQL codebase make it difficult to make outer joins work with semi-joins, and if we don’t get any real-world LEFT JOIN examples, chances are we will disable subquery optimizations if there’s LEFT JOIN in the parent select, or in the subquery, or in either case.

MySQL 6.0 news: Batched Key Access is in

Ok this isn’t very timely reporting, but about two weeks ago Batched Key Access feature has been pushed into MySQL 6.0. You can get it from the bazaar repo now (bzr branch lp:mysql-server/6.0), or wait several more weeks till MySQL 6.0.9 is released and get it from there.

Batched Key Access in a nutshell

BKA is about accessing tables in batches when running nested loop joins. The benefits of batching table accesses are that

  • “Remote” engines save on number of roundtrips
  • Disk-based engines do reads in disk order instead of randomly probing the table, which allows to be easier on disk cache and take advantage of prefetching

Batched Key Access only works if the used storage engine supports it. At the moment there is support for MyISAM, InnoDB, Maria, Falcon (these are disk-based) and NDB (this one is remote) engines.


At the moment there’s no manual chapter yet. There is a short introduction at Batched_Key_Access page on the forge and there are MySQL Conference 2008 session slides. The slides cover some benchmarking and give an idea about what kind of queries and dataset you need to get speedups with MyISAM/InnoDB. We’ve seen great speedup with NDB also but didn’t publish anything so far.


With Batched Key Access and condition pushdown, it is now feasible to create a remote table engine with decent performance. We have a remote engine, ha_federated and it doesn’t support BKA or condition pushdown and is a death by latency if you have queries that do not match the

SELECT * FROM table WHERE primary_key=const

pattern. I have a strong temptation to code a performance version of ha_federated myself, but have to resist it as there is subquery optimization work to be finished and optimizer “bugs” to be addressed.

This is now a rather low-hanging fruit, any takers?

A proposal for method of delivering optimizer bug fixes

Working on query optimizer bugs can be a rather frustrating experience. First, as soon as some query doesn’t run as fast it theoretically could people will consider it a bug. On one hand that’s great, you get a constant stream of user input, but on the other hand you end up with a whole pile of “bugs” which you can’t hope to finish.

What’s more frustrating is that even if you manage to create a fix for an optimizer bug, there are chances it won’t be allowed into next GA (currently 5.0.70) or approaching-GA (currently 5.1.30) release (GA is our term for “stable” or “release”).

The reason behind this is that most optimizer bugfixes cause the optimizer to pick different query plans, and there’s no way to guarantee that the fix will be a change for the better for absolutely everyone. Experience shows that it is possible to have a query that hits two optimizer bugs/deficiencies at once in such a way that they cancel each other out, and get problems when one of the bugs is fixed. A more common scenario is when the optimizer makes the best choice but it just doesn’t have all the info. The top five unknowns are

  • data distributions
  • correlations between data columns
  • correlations between data value and physical record order
  • highly selective conditions on non-indexed columns
  • hot/cold caches

Neither of those can be easily checked, so we’re very conservative and have the “no query plan changes in GA versions” rule.

The problem is that it turns out our GA releases aren’t very frequent and one may have to wait a looong time before the fix makes it into official GA release. Bazaar and its ease of creation of publicly-accessible branches have rectified the situation a bit but most users want a binary and also we don’t want to end up with the need to maintain 2^N branches after N optimizer bugs.

The proposal

This aims at query optimizer (“here’s a query which uses a non-optimal plan”-type) bugfixes that affect a small amount of code in small number of places.

  • We’ll put the fix into both GA and next-after-GA versions.
  • For next-after-GA version, just put the fix in, do not support the old behavior. That’s the only feasible long-term option, we can’t afford to support all behavior we’ve had at some point in the past.
  • For the GA version, make it possible to switch new behavior on and off. The default should be the old behavior (so we only put one “if” into the old execution path. Can one hope that *that* won’t break anything?).

The mechanism to turn on the new behavior will be server command line option, something like --with-bugfix=NNNN. It’s possible to ask to turn on multiple bugfixes by using the option several times:

mysqld --with-bugfix=37642  --with-bugfix=13356

or, in my.cnf syntax:


The code of GA versions doesn’t change much, so it should be tolerable to have, say, twenty “if (bugfix_nnn) {…} else {…}” branches. mysqld binary should only know numbers of bugs which it has switchable fixes for. If it is invoked with –with-bugfix=N where N is not a bug number it knows, it should print issue a warning, something like this:

[Warning] This version doesn't have ability to switch fix BUG#NNNN , see
[Warning]   http://bugs.mysql.com/check-version.php?binary_version=X.Y.Z&bug=NNNN.

Visiting the printed URL gets you to the bugs database which has information about which fixes appeared in which versions, so it can tell you whether your binary already has the fix for BUG#NNNN integrated into the code or you need to upgrade, in which case it can tell you what is the first version that has the needed bugfix.

-end of proposal-

Any comments or feedback on this scheme are welcome. Their impact will be greater if they arrive till September, 17. We’re having a developer meeting on Sept, 17-24 and I’ll try to get this discussed and some decision made about this.

EXPLAIN CONDITIONS patch available

I’ve made a patch that makes EXPLAIN show conditions that are attached to various points of the query plan. If you run EXPLAIN CONDITIONS (or EXPLAIN CONDS) statement, the output besides the usual EXPLAIN resultset will have a second resultset that will show

  • Conditions attached to individual tables
  • Conditions that are applied before/after join buffering
  • Table and index conditions that were pushed down into the storage engine
  • … and so forth (I believe it prints out all possible conditions that are there)

It looks like this:

mysql> explain conds select * from City, Country where City.Country=Country.Code and City.Name like 'C%' and Country.Continent='Asia' and Country.Population>5000000;
| id | select_type | table   | type | possible_keys     | key       | key_len | ref             | rows | Extra                              |
|  1 | SIMPLE      | Country | ref  | PRIMARY,Continent | Continent | 21      | const           |    1 | Using index condition; Using where |
|  1 | SIMPLE      | City    | ref  | Country           | Country   | 3       | db.Country.CODE |   18 | Using where                        |
2 rows in set (0.01 sec)

| id | table   | cond_type       | cond                           |
|  1 | Country | pushed_idx_cond | (Country.Continent = 'Asia')   |
|  1 | Country | where           | (Country.Population > 5000000) |
|  1 | City    | where           | (City.`Name` like 'C%')        |
3 rows in set (0.01 sec)

Unlike EXPLAIN EXTENDED, EXPLAIN CONDS doesn’t use excessive quoting or database prefixes before all columns. Excessive parentheses are still there, I have intent to remove them.

How you can get it:

Both the branch and the patch are made against mysql-6.0 tree. The code has some intersection with new 6.0 features, eg. it prints pushed index conditions, which are in 6.0 only, so the patch can’t be automatically applied to MySQL-5.x. The conflicts should be trivial though, the downport should be a question of removing all parts of the patch that break the compilation. If you need EXPLAIN CONDS in 5.x but can’t manage the downport, please let me know, perhaps I’ll be able to lend a hand.

Code and slides: Batched Key Access feature preview

At the moment, there are two big features in the works in MySQL optimizer – Subquery optimizations and Batched Key Access. While the former is a part of MySQL 6.0, I wrote about it here in my blog, and so forth, the latter was in nearly stealth mode until a couple of weeks ago.

That’s no longer the case:

  • Batched Key Access source code is now published as mysql-6.0-bka-preview tree.
  • Igor Babaev, the author of the feature, gave a talk about Batched Key Access at MySQL User Conference, and the slides are available here.
  • There is now Batched Key Access page on forge.mysql.com which a semi-official feature homepage which describes how you can get source, binaries, etc

If you’re developing any kind of “remote” storage engine or are using MySQL in a data warehousing application where disk IO is the bottleneck, I recommend to take a look.

Slides: New subquery optimizations in MySQL 6.0 (new revision)

I’m now at MySQL Conference and have just finished my New Subquery Optimizations in MySQL 6.0 talk. If you’re eager to get the slides, here is the link to the pdf file and a mirror.

The talk has the same name as this MySQL University session but the slides have been thoroughly re-worked, and also there is new content:

  • More detailed explanation of subquery handling in 4.1/5.x
  • More reliable/justified benchmark numbers
  • Observations about subquery demographics
  • An attempt at comparative analysis of how MySQL’s subquery strategies compare to PostgreSQL’s
    • And a slide #28 that explains why we’re looking at PostgreSQL all the time

Have a nice viewing. And, if you get subqueries that should be fast but aren’t, we’re now actively looking at every subquery performance bug we get and will appreciate any input.

On possibility to have more than 64 indexes

I was asked on Sun Tech Days whether it is possible to use more than 64 indexes in MySQL. I’ve asked my colleague, Ingo Strüwing, and found out the following: yes, it is possible to have more than 64 indexes, and no additional patches are required. You can get 5.0 source tarball, ./configure it with --with-max-indexes=128 and you’ll get a server that can use up to 128 indexes. One needs to be cautious though:

  • --with-max-indexes=N option looks like you could configure server with arbitrary maximum number of indexes. That’s not true, if you use N>128 then the server will compile but it will not even be able to start.
  • 128-indexes build of server is not widely used, so potentially it can be less stable than the regular build. My experience: I compiled current 5.0 source with --with-max-indexes=128 and it has passed all bundled tests (good) but I don’t see any tests that would use more than 64 indexes (bad). I’d recommend to do some testing before you roll out anything of value on 128-indexes build.
  • Work on 128-indexes version is documented here: BUG#10932.

Hope this information was helpful.

Sun Tech Days St. Petersburg

I’m now at Sun Tech Days St Petersburg. MySQL got involved in it on a very short notice so there are no MySQL sessions or tutorials, and we only have a small booth. It’s decently manned though, there are six MySQLers here in total and we have no less than three people on the booth at nearly all times.

We organized some ad hoc sessions, so far, Alexey (aka Kaamos)’s talk about MySQL and dtrace attracted the most listeners, and Kostja was the second with his talk about new MySQL 5.1 features.

We get lots of questions on wide range of topics, the most popular are

  • Is Sun going to change MySQL license (close the source)? People are pleased to learn that MySQL will remain GPL.
  • How is MySQL development going to co-exist with PostgreSQL development inside Sun?
  • What, exactly, are MySQL licensing terms? People are not sure whether they will be able to use GPL or will need an OEM license, and what are the OEM licensing conditions and terms. It would be nice if MySQL website had more details about OEM agreements terms.

We get a fair number of technical questions as well. Here’s a quick dump in no particular order:

  • Does MySQL have special optimizations for Sun Niagara processors? What are the settings one should use when running on Niagara?
  • A complaint about poor subquery performance for a query generated by Hibernate. We figured that the issue is addressed in MySQL 6.0 by WL#3740 table pullout optimization.
  • Quite a few people are interested in in-memory transactional databases. Not all of them know about MySQL Cluster.
  • Most people are not aware of existence of MySQL Forge or MySQL University. People were interested to see slides, talks and other similar media, we’re handing out pieces of paper with MySQL Forge urls.
  • How does one create a load-balanced and/or fault-tolerant multi-machine MySQL installation? There is MySQL Proxy and there are 3rd-party solutions, there are also clustered JDBC drivers, but people would like to see a packaged or officially supported configurations. It would also be nice if there was an overview of available solutions and their relative properties.
  • MySQL’s command line client has poor tab completion in comparison to PostgreSQL’s. Indeed, hitting <TAB> in /usr/bin/mysql client always completes the list of tables and columns while in /usr/bin/psql completion is context-sensitive, e.g. if you type ALTER and hit TAB you’ll get a list of continuations available ALTER commands.
  • Is there a way to have more than 64 indexes?
  • Does MySQL’s GIS implementation allow to efficiently select primitives that are contained within (or have non-empty intersection with) a given axis-aligned rectangle? WL#3126 GIS: Precise spatial operations source tree needs to have some user-level documentation.
  • mysqldump and charsets. Quite a few people complained about having difficulties when doing export and import of databases in cp1251.
  • There needs to be some tool to assist upgrades. Several attendees said they were unable to upgrade their applications: the application stops working when moved to a newer MySQL version and they can’t figure out what went wrong. Ideally, they would like to have a tool that would just take their source code and update it with a newer MySQL, but that seems to be an “AI-complete” task. As a realistic option, we might use Proxy to run all queries against both old and new server, and report the differences.
  • MySQL cannot use index_merge over both fulltext and non-fulltext indexes, that is, it will use only one index (actually the fulltext one) for query like
    SELECT count(*) FROM tbl
    WHERE match(ft_key) AGAINST ('pizza' IN BOOLEAN MODE) AND non_ft_key='bar'

The conference is definitely worth to have presence on, also it’s nice to have enough people so we don’t have a strict booth watch schedule and there is always some rested force on the booth. Day-wise, the first day was the busiest, starting from the very opening hours. The second day had a slower start and even now (13:00) the crowd is not as thick. The good side of that is that it’s easier to have longer and uninterrupted discussions with interested attendees.