Databases Reference
In-Depth Information
+--------------+----------+----------+----------+
| 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 “Transac-
tions and Locking” in Chapter 7, we saw how transaction support can be useful for
some applications. However, transaction support adds overhead to database opera-
tions. 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 Chap-
ter 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.
We won't discuss performance any further in this topic, but if you're setting up a pro-
duction database site, it's definitely worth looking at the resources listed in the next
section.
Resources
Database server tuning is a complex art, and is largely beyond the scope of this topic
To learn more about tuning MySQL, we recommend the following resources:
• The MySQL Manual: Optimization (
http://dev.mysql.com/doc/refman/5.1/en/opti
•
MySQL Database Design and Tuning
by Robert D. Schneider (MySQL Press)
•
High Performance MySQL
by Jeremy D. Zawodny and Derek J. Balling (O'Reilly)
Exercises
1. Why is it important to allocate a large value to
read_buffer_size
?
2. What is the advantage of caching query results?
3. What does the
OPTIMIZE TABLE
command do?
