MySQL 4 ready to take on the 'big boys'?
Ian Gilfillan is the lead developer for IOL and author of “Mastering MySQL 4″.
MySQL 4 is almost here. As I write this version 4.0.9 is the latest version, and it’s a gamma release (meaning they think they’re stable, but not willing to say so just in case something pops up at the last minute). Version 4.10, which will probably be out by the time you read this, is scheduled to be the first stable release of version 4.
So whats all the fuss about? You may know MySQL as a fast, lightweight database but missing many of the features “real” databases have. Well that’s all changing.
MySQL 4 comes with a fully ACID-compliant storage engine. ACID stands for Atomicity, Consistency, Isolation and Durability – features considered critical for data integrity. MySQL could never reliably be used for critical data transactions before this, and supplying this feature as a standard part of MySQL 4 takes care of one of the primary criticisms of the old versions.
MySQL originally gained popularity with website developers due to its speed in that kind of environment. The default, non-transactional MyISAM table types are extremely fast in a high-read, low-write environment, such as a website. And now MySQL 4′s new query cache can double the speed of commonly-issued queries. Although benchmarks can be misleading, in the independent E-week benchmark, MySQL came in slightly behind Oracle, well ahead of SQL Server, DB2 and Sybase. Although the strengths and weaknesses of the benchmark can be debated, there’s no doubt that MySQL 4 is an extremely fast database. This has always been the main focus for the developers.
Although MySQL was originally developed to run on Unix platforms (specifically Solaris), it has been ported to run on most operating systems. You can now get binaries for Linux, Microsoft Windows, FreeBSD, Sun Solaris, IBM’s AIX, Mac OS X, HP-UX, AIX, SCO OpenUnix, SGI Irix, and Dec OSF. If that’s not enough to satisfy you, being open source, you can download a copy of the source code and compile it on any other platform you choose.
Administering MySQL has always been relatively easy. Not in the Windows sense that you can click and go but find yourself in trouble when it comes to doing something behind the scenes, but rather that the administration tools are well-designed, flexible and are not based on old legacies. For example, all permissions are controlled by a database and the tables within that database. You can easily control access at username, hostname, database, table or field level by making amendments to the relative tables, or directly issuing a GRANT SQL command.
To optimise the server, a single configuration file contains a number of variables that a skilled administrator can use to get the most of the available hardware. Version 4 allows more permission flexibility, support for SSL encryption, and allows the server variables to be changed while the server is still running.
MySQL 4 has also added a useful enhancement to their fulltext indexes. The old version allowed simple searches on keywords, sufficient for most people searching for content. With version 4, MySQL allows for boolean searches, a powerful enhancement catering for portions of words, phrases, weighting the relevance of various words in the search and so on.
MySQL has a flexible licensing arrangement, whereby you can use a GNU version (freely available, but all related code must also be released with the same license), or a commercial license with no release restrictions.
But MySQL still has some disadvantages. Sub-queries, a common (though often abused) ANSI SQL feature will only be available in version 4.1 (which was released as alpha this month). Stored procedures and triggers will only be available in version 5, and views in version 5.1.
It also does not yet have full Unicode support. These features are serious impediments to MySQL’s adoption at the enterprise level. The MySQL team has spent a great deal of time in the past justifying why triggers, views, sub-queries and transactions are not necessary, much to their detriment. For many purposes, they are vital, and a too stringent denial of their functionality has left MySQL with a lingering legacy.
Many people see it as a toy database, and many in the open source world are drifting towards the more fully featured PostgreSQL. But there is no standard ANSI feature that MySQL is not planning to support, and the great strides they have made in a short space of time mean that Oracle, and more so DB2, SQL Server and Sybase must be worriedly looking over their shoulders.
South African authors latest MySQL 4 book