Other Things to Consider | 391

+--------------+----------+----------+----------+
| music.artist | optimize | status | OK |
+--------------+----------+----------+----------+
1 row inset 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, 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 book, but if you’re setting up a production 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 book
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
mization.html)
• 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. What does the OPTIMIZE TABLE
command do?
392 | Chapter 12:
Configuring and Tuning the Server

Download 4.24 Mb.

Share with your friends: