Optimizing MySQL - Database Design Manual: Using MySQL for Windows - page 154

Figure 13.7 Optimizing a query by adding an index.

If you compare the results from this EXPLAIN shown in Figure 13.7, with the results
from the previous EXPLAIN in Figure 13.3, you will notice that the log row now has the
index entry in the possible_keys column instead of NULL. So if we were running this query
on a huge set of data, the index would optimize this query, and make it run better.
Indexes not only make a difference to a join, in fact they can also make a difference to
any retrieval of rows where you are restricting the output by values in a column. If the index
refers to a column that is being used by the query, then the retrieval will be quicker.

OPTIMIZE TABLE

As mentioned when discussing datatypes, the constant insertion and deletion of records in
variable length column types causes the table to become fragmented due to entries and
space becoming de-allocated and re-allocated. This fragmentation can cause query opera-
tions on the table to be slower than expected. To remedy this, use the following:

OPTIMIZE TABLE tablename

You can optimize more than one table at once by separating the table names with com-
mas. OPTIMIZE TABLE will defragment the table's datafile and recover any wasted space.
It first repairs any split or deleted rows if it finds them, then sorts the index pages if they are
out of order, and then updates the table's statistics if the index sort did not complete the
repair. Because optimizing a table can involve moving large parts of the table around,
MySQL stops other processes from changing the table while it is running. The complex data
manipulation could be hindered by another user trying to insert something into the table
during the operation.
For some reason, the graphical MySQL client does not run the OPTIMIZE TABLE com-
mand, so the output when run in the MySQL monitor is shown in Figure 13.8.You will
notice from the figure that if the table is already optimized, MySQL does not attempt to
optimize it again, as we can see from the second time that we run the command in the
figure. If this is the case, it lets the user know that the table is up to date. The insertion or
deletion of a row in the table will allow you to run the OPTIMIZE TABLE command again
should you require it. However, there is little need to run this command immediately after
you have just run it.