Since version 5.0 of MySQL was released in October 2005, a lot of new features have been added to it to make it standards-compliant, secure and efficient. In 2011, MySQL 5.5 is still an open source software and is freely downloadable for various platforms. In this blog entry, I will focus on performance in MySQL.
MySQL provides two important utility statements, EXPLAIN and PROCEDURE ANALYSE, which can be used to understand where performance bottlenecks are. EXPLAIN is used to obtain information about how MySQL executes a SELECT statement. PROCEDURE ANALYSE suggests optimal data types for each column that may help reduce table sizes. Also, as MySQL exposes its inner workings to the database administrator, one can go very far in optimising performance.
One simple way to increase performance is to create indexes as they generally allow queries to run more quickly. Care must be taken to only create indexes when really needed as indexes consume space. In some special case, indexes can, in fact, decrease performance.
In the real world, web applications are used by concurrent users and it is important to use transactions and proper locking mechanisms in order to preserve the integrity of data. Of course, it is also important to understand that transactions and locking can have a negative impact on performance.
Whenever the same sequence of SQL statements is done over and over again, there is an opportunity to use a stored procedure which is just a set of SQL statements stored in the database server itself. Consequently, clients don’t need to issue the individual statements but can refer to the stored procedure instead. As written in the MySQL documentation, stored procedures can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side.
One recently added feature of MySQL is partitioning. This allows different portions of a table to be stored as separate files in different locations on disk. The rule used for doing the partitioning is specified by the user. Some queries can run much more quickly when a table is partitioned in virtue of the fact that data satisfying a given WHERE
clause can be in one (or a few of the) partition(s), which obviously excludes any remaining partitions from the search.
Finally, given the prevalence of storage systems with very high I/O rates compared to our classical hard disks (e.g. solid state devices), MySQL has an adjustable I/O rate which can be tweaked to maximise performance.
We will cover some of these interesting aspects of MySQL in our coming Web Development with PHP and MySQL training starting on 25 July and 8 August.
One thing is sure: MySQL is far from being a toy.