“The key, the whole key, and nothing but the key, so help me Codd!”

The database guys should like that one. Can’t remember where I saw it today to acknowledge reference.

Today MySQL published the press release with results of Database Contest (results on German available here http://www.mysql.de/ct-dbcontest).

Peter and me spent quite some time working on this project while being employed by MySQL and it is great to see results finally publicly available.

The story began about year ago when C'T magazine had called for Database competiton using Dell DVD Store benchmark (details available here: http://firebird.sourceforge.net/connect/ct-dbContest.html).

Most interesting results are (more orders per minute are better ):
MySQL5/PHP (our solution) : 3664 orders per minute
DB2/Java : 1537 opm
Oracle / Java: 1412 opm
…

Peter Zaitsev over at the excellent MySQL Performance Blog recently wrote an article on duplicated and redundant indexes -- any indexes which cover exactly the same columns as another index, or cover a leftmost prefix of another index. While there are subtleties, such as FULLTEXT indexes not being the same as non-FULLTEXT, for the most part this is sufficient criteria to raise possible duplicates to a DBA's attention. I opened my big mouth in the comments and said I could write a quick Perl script to discover possible offenders in just a few lines of code. Once I did that, I had to do it and give you the script. Here it is.

I'm giving a webinar on tag schema designs and folksonomy concepts tomorrow, from 1pm to 2pm EST. The webinar is chock full of SQL code and optimization tips, so it should be a fun time ...

We'll go over tagging database access patterns, many-to-many mapping techniques, using MySQL's storage engines and replication effectively to scale out the schema, and discuss the advantages of a normalized schema approach versus other popular approaches. Oh, and we'll make fun of rounded corners, too.

Beijing, August 29 2006 - MySQL AB, the developer of the world's most popular open source database, and Greatlinux Inc, a leading open source solutions and services provider in China, today jointly announced a strategic partnership to deliver commercial MySQL-based solutions to the burgeoning Chinese market.

I just uploaded the first ready-to-use package of the MySQL Index Analyzer (version 0.02). You can download it via its download site.

I recommend going to the MIA homepage to see what's new and how to use it.

Recently I was reminded painfully of the fact that habits you have taken to once hardly ever get laid off.

I usually consider myself someone who tries to write software after I have thought it through. I do not mean "over-engineering", "over-abstracting" and "over-prepare-for-anything-that-might-ever-come'ing". However I also believe that starting hacking blindly is not a good thing either. And I try to write "nice" code, even though it might be a little more work, as long as it is easier to read or just plain more stable (which is often the same).

Sometimes however, especially under a tight schedule, by force of habit I (and probably any developer out there) tend to do things that upon later review make me feel deeply embarrassed. Just so I did a couple of weeks ago...

I had to write a component that translates data from a legacy system, stored in plain text files, into a relational database, accessed through an object …

mysql> select count(*) from file_to_delete;
+----------+
| count(*) |
+----------+
| 40510413 |
+----------+

mysql> select count(*) from file_on;
+-----------+
| count(*) |
+-----------+
| 178473804 |
+-----------+



Was wondering about this:

host device size(G) used(G) free(G) use%
---- --------------- ---------- ---------- ---------- ------
[...]
---- --------------- ---------- ---------- ---------- ------
total: 3437.986 2722.549 715.437 79.19%


when:

mysql> select count(*) from file;
+----------+
| count(*) |
+----------+
| 19229098 |
+----------+

mysql> select (sum(length) / 1024) / 1024 from file;
+-----------------------------+
| (sum(length) / 1024) / 1024 |
…

The results are in, and MySQL came out on top. C't just did a comparison of different databases and found that MySQL is the "fastest database application." The magazine's editors held the contest to evaluate database performance in real-world business use by creating a standard online inventory system.

From the announcement:

"The DVD shop created by MySQL is the hands-down winner of the performance crown. It clearly demonstrates the capabilities of a carefully configured MySQL/PHP application.”The MySQL DVD online store was able to process 3,664 orders per minute (opm). If a second computer had been used, the figure would have gone up to 6,000 opm, indicating that MySQL is business ready. No more of this silly nonsense about it being a low-end utility database. Nothing could be farther from the truth.

(You can see the full results from the contest …

So I've set up this cast-aside laptop as a machine to do pulls from BitKeeper and do builds and installs and it's working pretty well. I was able to do pulls of six different versions of MySQL and build them. They are now happily running simultaneously.

Then I went away for three days, taking my younger daughter to boarding school where she is starting as a freshman in high school. At the same time my older daughter is off to college so the nest is now basically empty. But I digress.

I said 'was' because shortly after that a couple bugs manifested, one as noted before and one new one which I was pleased to report and have verified. I have been following the …