MySQL has come along by leaps and bounds, and the new version 4.0 is barely recognizable when compared with its earlier siblings. I first started using MySQL 3.22, when it was very much a toy, used version 3.23 extensively for websites, but version 4.0 and beyond promises much more. This article is a roadmap showing you the new features already implemented, and those still to come. If you have ever rejected MySQL as a product lacking in required features, maybe it's time to take another look.

New features in MySQL 4.0

MySQL 4.0 is the latest production release of the MySQL database, and it is a huge improvement over the old version 3.23. Version 4.0.12 was pronounced stable in March, 2003. Here are the main new features:

• Perhaps the most-awaited enhancement is to the FULLTEXT index. This creates an index on a text field, and provides a powerful, flexible mechanism for performing boolean searches on this. In my experience, I've often had to index and access text data, and the FULLTEXT index is much better than any of my attempts. Many solutions are limited to full words only, while the FULLTEXT index has no such limitation, allowing you to weight words or portions of words. You can read more about FULLTEXT indexes in Using Fulltext Indexes in MySQL, Part 1 and Using Fulltext indexes in MySQL, Part 2
• Supports the ANSI SQL UNION statement, which combines query results into one result set.
• Can perform multi-table UPDATEs and DELETEs.
• There are other non-standard new statements, familiar to users of other DBMS', such as IDENTITY and TRUNCATE TABLE, as well as new functions such as FOUND_ROWS(), which return the number of records that would have been returned without a LIMIT clause.
• Many of the MySQL server variables can be changed without restarting the server, which, while useful, sounds more useful than it is because a reboot will restore the old settings.
• The InnoDB storage engine is now a standard feature of the server, and no longer an optional extra. InnoDb is the table type that allows ACID-compliant transactions, as opposed to the default MyISAM table type, which is faster for general use, but not that useful for critical operations. InnoDB tables use row level locking, which means that an update to a record only locks that one record, not the entire table. Locking the entire table is faster when most of the database accesses are selects (as with most websites), but is slower when the number of inserts and updates are closer to the number of selects. For a long time, critics of MySQL had valid problems with the security and consistency of MyISAM tables, and the ACID compliant InnoDB table goes a long way to resolving these.
• MySQL 4.0 can be faster in certain circumstances. This is mainly achieved through a query cache, which caches repetitive query results, although many mature applications perform their own caching on a code level. There are other minor speed improvements to certain statements as well.
• MySQL comes with an Embededded Server library, allowing applications to be deployed with MySQL as the underlying database, such as on CD's, kiosks, fridges or even pencil sharpeners (yes a data storing pencil sharpener is coming soon, mark my words).
• MySQL now supports an extra character set - latin1_de, which ensures correct sorting of German words.
• MyISAM tables now support symbolic linking at a table level, so Windows users can create symlinks at a table level (this was always available to Unix users)
• The security model has been enhanced, allowing the administrator to grant permissions more finely. New permissions are those allowing users to create temporary tables, lock tables, perform certain replication tasks, view all existing databases, connect even when the maximum connections have been reached (useful for a DBA to enter and perform emergency surgery) and even permission to run stored procedures (which are only coming in version 5). A DBA can also limit users to a certain number of connections, updates or queries per hour.
• MySQL 4 runs natively on Novell Netware 6.0.

That's quite a list, and of course each of the versions to-date have changelogs that are at least as long as the above lists, with minor bugfixes and feature additions. However, the main two are the ACID compliancy as a default feature (many DBA's scorn to touch systems that cannot support this), and the FULLTEXT indexes.

New features in MySQL 4.1

To some users, MySQL 4.1 is potentially even more exciting an upgrade than MySQL 4.0. MySQL 4.1 is currently out in alpha, only for the experimenters, and not yet ready for production use, although medium to long-term developments could be appropriate.

• MySQL 4.1 supports subqueries. Many queries can be more efficiently written without using a subquery, but there are the exceptions, and subqueries are a standard ANSI SQL feature, so it's about time MySQL supported this.
• Supports Unicode (UTF-8), allowing for much more extensive internationalization. Different character sets can also be set per column, table or database, which could be necessary if the data stored is from many languages.
• Support for geographical data (OpenGIS)
• As if one wasn't enough, MySQL 4.1 can now send multiple warnings to the client (useful for bulk data processing).
• More minor speed improvements (probably counteracted by all the extra bulk MySQL 4.1 now carries - it's a bit early to tell whether these will be noticeable).
• Although the MySQL manual is one of the best manuals out there, MySQL 4.1 comes with a HELP command, applicable only to that version.
• Support for derived tables, for example:
  SELECT table1.field1 FROM table, (SELECT * FROM table2) table3 WHERE table1.field1=table3.field1
  
• Support for multi-line queries, which allows you to run several queries, and then read the results afterwards.
• Various maintenance statements will be stored in the binary log, meaning you may be able to simplify your maintenance tasks when replicating.
• CREATE... LIKE allows you to easily create a new table with the exact structure of an existing table.

MySQL 4.1 is probably the first really 'grownup' version of MySQL. The feature list contains some impressive additions. Geographical data, sub-selects, derived tables - all unthinkable only a few years ago, and perhaps for the first time the Oracles of the world will start to look over their shoulder.

New features in MySQL 5

• MySQL 5.0 will support the long-awaited stored procedures. Although many MySQL developers don't know what all the fuss is about, as there's little that currently can't be done in conjunction with an application language, stored procedures is an ANSI SQL standard used by developers from most other database environments, and it's an overdue addition for MySQL. Syntax will apparently be similar to Oracle PL/SQL and T-SQL.
• Triggers (basically a stored procedure called when a certain event occurs)
• Support for cursors
• True VARCHAR support (there is a long-standing MySQL VARCHAR bug, which may be solved by this)
• Accessing geographical data will be eased by support for RTREE indexes in MyISAM tables.

New features in MySQL 5.1

Here's where the crystal ball starts to get a bit cloudy, but there are still a few features in MySQL's roadmap not yet implemented, and due for inclusion with version 5.1.

• Foreign key support for all table types.
• Online backup (allowing the addition of replication slaves without bringing down the master server)
• The BIT type will really be a bit, and not take 1 character, as at present.
• Fail-safe replication (yes, the implication is true)
• Column level constraints

New Features in MySQL 6

- Views. This commonly-requested feature is only scheduled for Version 6. Views allow users to view a set of tables as a single table, with associated access rights. A subset of this feature is an unamed view, more commonly called a derived table, which is implemented in version 4.1

MySQL's roadmap is clearly designed to attract new users to the database based upon the level of development planned for the future. When I wrote the book Mastering MySQL 4, most of the 4.0 features were still new or under development, and it looks like I'll have to do an upgrade a lot sooner than I'd expected!

» See All Articles by Columnist Ian Gilfillan