Optimizing MySQL - Even More Optimization... (
Page 5 of 6 )
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. ;-)
