About MySQL development (Optimizer mainly)

If you use the EXPLAIN SELECT statement to see how your subqueries are treated by MySQL, you may sometimes meet the "unique_subquery" optimization. Here is how the manual describes it: " unique_subquery : this type replaces ref for some IN subqueries of the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr); unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency". Few weeks ago, while I was reviewing a patch fixing a bug in unique_subquery, I got a "simplification" pulsion. I told myself that:  unique_subquery is an optimization for a special case of simple subqueries (single inner table, using index, no aggregates); we have a more general system around, used for more complex subqueries, naturally capable of handling simple ones too if we wanted; this general system does not have the bug in question... Then I wondered: what if we removed the unique_subquery optimizat...

In this blog post , my colleague Jørgen Løland described a new feature of MySQL 5.6: Optimizer Tracing . I recommend reading his article, as it presents this new feature in a simple, easy-to-read manner. The Optimizer Tracing feature can help understanding what the Optimizer is doing; it is available since milestone 5.6.3, announced October 3rd at Oracle Open World (here is the changelog ). It's good to see it mature now; I remember that Sergey Petrunia did the first prototype back in March 2009! Today  I will be giving some must-have tips related to handling big traces. First thing to know, a trace lives in main memory (internally it is allocated on the heap or free store of the MySQL Server). An SQL statement which gives the optimizer a lot of work (for example, by joining many tables) will generate a large trace. Up to gigabytes in some pathological cases! To avoid hogging memory then, a trace will never grow beyond the value of the @@optimizer_trace_max_mem_size sess...

Hi all! Here is a new blog; I'll post here some thoughts, tutorials... all related to my job - software developer at Oracle Corporation, working on MySQL . It will soon be nine years since I joined MySQL AB. After working on interesting topics: Replication, Online Backup, the Maria storage engine, each of them having taught me something, I'm now full-time on the Optimizer, and guess what - it is an interesting topic and it teaches me something!