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 …

The work on subquery optimizations goes on, and we're already curious how the assortment of new 6.0 subquery optimizations compares to what is found in other DBMSes. MySQL's new strategies are not DBMS theory breakthroughs, similar ideas have been implemented in other DBMSes, but when you take this list of strategies and put them into this kind of optimizer, the result has no direct equivalents in any other DBMS (or at least we believe so).

The first thing we did was to take a look at PostgreSQL as it is easily available and seems to have at least decent subquery handling (or even better than decent, I have not heard much complaints). And the first interesting difference was handling of correlated subqueries. With exception of materialization, MySQL's new subquery strategies do not care if the subquery is correlated or not.

For example, …

As I wrote earlier, we (me and Ranger) have done an assessment of the impact of new 6.0 subquery optimizations. First, we've done a search for performance issues in MySQL's bug database. The raw list is here, and the here is a summary chart:

Search for customer issues in MySQL's issue tracker system has produced a similar picture (raw list):

Search in DBT-{1,2,3,4} open source benchmark suites produced a radically different result though (raw data is …

With the acquisition of MySQL I expect that many people might end up comparing MySQL and PostgreSQL using sysbench. It is like a micro-benchmark utility which includes an oltp mode which is used quite a bit to show MySQL performance. It can actually also be used with PostgreSQL. So this post is about how to configure sysbench to work with PostgreSQL. (Primarily a note for myself since I had to do hunt around to get it configured for PostgreSQL).

 
First download the latest version of sysbench. I had downloaded the version sysbench-0.4.8. After gunzip/untar I had to to figure out few steps to get the right configure script for it.

I am going to use the Sun Studio Compiler (since PostgreSQL in my case is also built with Sun Studio Compiler). So I will need the compiler cc in my …

It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

I?ve recently done a quick scalability test with MySQL 5.0 and 5.1 to check the new auto-inc patch with InnoDB and to see how MySQL 5.1 scales with InnoDB:

New in MySQL 5.1: innodb_autoinc_lock_mode = 1 (?consecutive? lock mode)
With this lock mode, ?simple inserts? (only) use a new locking model where a light-weight mutex is used during the allocation of auto-increment values, and no AUTO-INC table-level lock is used, unless an AUTO-INC lock is held by another transaction. If another transaction does hold an AUTO-INC lock, a ?simple insert? waits for the AUTO-INC lock, as if it too were a ?bulk insert.?
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-increment-configurable
This fixes the …