Learning MySQL

Other Things to Consider

There are many other aspects of database and application design that you can look at when considering performance. For example, if you make large-scale changes to a table (for example, by deleting many entries), you are likely to get better performance if you run the OPTIMIZE TABLE command to reorganize the table file on disk. This is especially true if the table contains variable length fields:

mysql> OPTIMIZE TABLE artist;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| music.artist | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.06 sec)

In most cases, the MySQL server handles this adequately, so you won’t need to use this command often, if at all.

Careful design of tables and indexes can also help improve performance. In Transactions and Locking” in Chapter 7, we saw how transaction support can be useful for some applications. However, transaction support adds overhead to database operations. If you need transaction support in some tables, but not in others, you can use different table types within a single database. In The EXPLAIN Statement” in Chapter 8, we saw how indexes can help increase the speed of queries. Try to minimize operations that scan all rows in a table, and try to add indexes that can be used by frequent queries. Shorter keys are generally faster to use, so try to keep the length of primary keys down. ...