Monday, March 24, 2008

Shoot in the foot

I've just finished reading two recent blog posts about new query optimizations in the upcoming MySQL 6.0, it's all fine and dandy but ...
Looking at Correlated semi-join subqueries and PostgreSQL by S. Petrunia we can read something like
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).
Does this mean their benchmark is PostgreSQL? I mean, Oracle and SQLServer are easily available too ... not to mention the comparison any reader can see a few lines below between MySQL's and PostgreSQL's explain plans.

The other amusing read is New optimizer features in MySQL 6.0 again by Sergey where you can find an interesting speed comparison which (might) boil down to

Server version Wallclock time # of reads
MySQL 5.1 12 min 9,001,055
MySQL 5.2 1.8 sec 153,008
MySQL 5.2 no_semijoin 25 sec 7,651,215
PostgreSQL 8.2.5 0.1 sec 2,413

Very interesting!

But anyone interested in query optimization should take the time to check the whole paper, extremely informative (and much of it's content is good for any database too)!!!


BTW MySQL 5.1 is not in production currently, while PostgreSQL 8.2.5 has just been superseded by the new PostgreSQL 8.3.1

1 comment:

Sergey P. said...

> Does this mean their benchmark is PostgreSQL? I mean, Oracle and SQLServer are easily available too

Doesn't Oracle forbid publishing benchmark data for their database? The post in question does not have benchmark data but I don't want to be denied an option to run some queries and publish their execution times.

Re the SQLServer, one needs to have Windows to run it.

In any case, I'd prefer to compare against an open source dbms as that gives an option to go look into the source code if one encounters some unexplained phenomena.

My impression so far was that PostgreSQL is a nice DBMS (with a decent optimizer), good to compare against.

If you know another open source DBMS which would be a better comparison target, I'm open to offers :)