Kevin Burton’s NEW FeedBlog

MySQL 5.0 is now gold!. Great job guys!

MySQL is really moving forward towards an Enterprise (I hate that word) -quality database.

The main problem in my perspective is that MySQL 5.0 didn’t focus on any features that most hard-core DBAs care about. One of the reasons I like MySQL is that the feature set is at a sweet spot for developing (somewhat) scalable web architectures.

The problem is that for production systems it’s missing a LOT of functionality.

From my perspective MySQL really falls down in the following areas:

Concurrent ALTER TABLE

Have a 5 terabyte database? Need to add a column? Good luck! You’ll need to lock your database for 72 hours while MySQL re-indexes all your records and copies ALL your data on top of itself. This is true of both INNODB and MyISAM tables. If you have a 5T database you’ll need 10T to perform the ALTER.

Want to change the default value of a column?. Even though NO data needs to be modified MySQL isn’t smart enough to just modify the schema. It has to copy the table all over again and re-index itself.

Replicated Session Variables and Concurrent ALTER TABLE

Now let’s say you’re working with your 5T database and you run an ALTER TABLE. You can crank up a few variables so that the alter works faster. Of course this command will be replayed on your slaves which won’t have the updated variables and so will run 100x slower. You can of course run KILL and start the ALTER table manually but this will probably BREAK replication on that slave.

What would be better of course would be to run concurrent ALTER TABLE on the master and all your slaves at the same time and with the same values for some system variables.

Right now you can do it manually but it certainly isn’t documented.

BIT indexing that actually uses a BIT!

Right now a bit isn’t a bit. It’s eight bits (one byte). Fun! For many applications this can waste a LOT of data. For a lot of my schemas I’ll want to buffer as MUCH of the index in memory as possible and with BITs I’m wasting 7x of my memory!

IN clauses with subqueries using indexes

This is just insane. In MySQL if you write a subquery like:

SELECT * FROM FOO WHERE ID IN ( SELECT FOO_ID FROM BAR )

IT won’t use an index. Fun. Full table scan baby! If you have a 5T table you’d have to scan the full table.

These areas are important. While these new features help MySQL AB sell into the enterprise market, existing users need MySQL to solve real-world problems. These are fairly large problems that just need to be fixed.