After reading part one and two of our Database Normalization and MySQL series, we could use a little follow up.In this article, we'll discuss those facets of optimizing a MySQL server that relate directly to it's compilation, configuration and subsequent administration.
After all we’ve discussed so far, there is still more to be covered. The final concept that I’ll discuss is the analysis and optimization of the MySQL tables using several of the database server’s own built-in commands. Let’s begin this section with an overview of those commands that help you to analyze what.
SHOW
You might be interested to know that you can view a summary of what has taken place on the MySQL server by simply executing:
mysql>show status;
This will result in a rather lengthy list of status
variables and their respective values. Some of these variables include the number of aborted clients, number of aborted connections, number of connection attempts, maximum number of simultaneous connections, and a slew of other very useful information. This information is invaluable for determining system problems and inefficiencies.
SHOW is capable of doing much more than just displaying information regarding the MySQL server as a whole. It can also display valuable information about the log files, specific databases, tables, indexes, processes, and the privilege tables. Check out http://www.mysql.com/doc/S/H/SHOW.html for more information.
EXPLAIN
When prefacing a SELECT command, the EXPLAIN explains how that SELECT command is processed. This is not only useful for determining whether or not you should add indexes to a table, but also for determining exactly how a complex join is processed by MySQL.
OPTIMIZE
The OPTIMIZE function will allow you to recover space and defragment the data file, which is particularly important after a substantial number of updates or deletes have taken place on a table consisting of variable-length rows. OPTIMIZE currently only works with MyISAM and BDB tables.
There are several other very useful functions geared towards helping you coax your tables into tip-top shape. However, those discussed thus far should keep you busy for the moment. ;-)
