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. ...
Get Learning MySQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.