MySQL 5.1

MySQL 5.5 is available but MySQL 5.1 is the default in Ubuntu Linux and a few other systems. What are the advantages of MySQL 5.1 over older releases? Is there any reason to upgrade to MySQL 5.5?

MySQL has the advantage of containing several storage engines providing you with what is effectively several different types of databases in one software package. You install MySQL then choose MyISAM or InnoDB or one of the other storage engines.

MySQL defaulted to using MyISAM up to MySQL 5.0. MySQL 5.1 defaults to InnoDB. If you upgrade from an earlier version, your databases will remain unchanged. When you build a new database, the database will use InnoDB instead of MyISAM.

Years ago MyISAM was faster than InnoDB for most uses. InnoDB went through many upgrades including performance enhancements. The InnoDB in MySQL 5.1 is often faster than MyISAM because InnoDB uses row locking instead of table locking.

What does row locking mean in practical terms? Row locking a nd table locking do nothing when you read the database. If everyone is reading and nobody is updating the database, you see no difference. When every request is an update and none of the requests are reads, row locking is slightly slower because the database has to record a lock for every row being updated and has to check for existing row locks.

The most common situation is a mixture of reads and updates. Row locking slows down the reads and updates because the reads have to check for row locks. In most cases the reads and updates will then save time because the row they want is available. Compare row locking to the table locks in MyISAM where a whole table is locked by one update and all the other updates for that table have to wait. The little bit of row locking overhead avoids the common situation of a whole web site slowing down for updates on the most popular table.

Partitioning

Do you have a large active table in your database? Is the table a bottleneck? In the old days, back when people had to suffer with an iPhone 1, you had to convert from MySQL to PostgreSQL to use partitioning, a neat way of supercharging your database. MySQL 5.1 lets you partition tables. relax, you can now expand your database without changing brand.

Partitioning has limited effect. you have to understand how it works and provide the right resources to make it work. If you are expanding from one computer to several computers and want to use all the computers for your database, you probably want replication, not partitioning. Partitioning is used when you expand from one disk to several disks. You split your big busy table into several partitions and place one partition on each disk. The table activity is then shared by several disks. Going from one disk to five disks could make the database five times faster.

Partitioning can also be used to expand a table beyond the size of a disk or a RAID array. If your disks are limited to 2 GigaBytes and your table is 5 GB, you could spread the table over three disks by using three partitions.

Partitioning always expands the available space but may not expand the space or speed as much as you think. If all your table updates are insertions of new rows, the expansion will be on the last disk and be limited by the size of that disk. If all the updates are on the most recent rows, all the activity will be on the last disk and not spread over all the other disks.

How do you spread the activity over all the disks? Assume you are recording purchases in a shopping cart and the table is partitioned based on the first digit of the purchase number. The current purchase number is 6000000 and goes into partition 6. All the new purchases will start with 6 and create a bottleneck in partition 6. your partitioning will fail.

What would happen if you partitioned based on the last digit in the purchase number? Purchase 6000000 would go in partition 0, purchase 6000001 would go in partition 1, and so on. You would have partitions 0 through 9 and the workload would be evenly distributed over all the partitions.

Partitioning works when you have multiple physical storage devices and none of them have competing workloads.

Other changes

Almost all the other changes are irrelevant or require no action.

Clustering is no longer in the base MySQL product. If you use clustering, you download a different product. This will make the MySQL download smaller for most users. If you use clustering, you choose the other download instead of the standard download.

XML functions are included in MySQL. In most cases, you will process XML in your code before storing the XML in the database. If you store articles in XML format in your database and need to access the articles by author, date, category, and other values, you extract the values from the XML in your code than add the values to the database row with the XML text. You can then index the extracted values and have high speed lookup. The XML search may be of use for the occasional search or the building of a new index.

There are a lot of changes where you get minor new options on things that will not change if you continue doing the same thing with the same SQL. Some database administrators will be able to use some of the new features for big databases. Most Web site administrators will not find anything useful outside of partitioning.

The full list is at mysql.com in What Is New in MySQL 5.1.