SO FAR, WE'VE CONCENTRATED MOSTLY on database design and writing queries through this book, and we'll continue to discuss aspects of those in this chapter. But there are other areas where you can work to improve the performance of MySQL and MySQL-backed applications. This chapter addresses those areas. For example, many aspects of the MySQL server's operation can be modified by setting configuration variables. Although their default values are often "good enough," sometimes changing these can make a big difference in performance. In addition, you can obtain a lot of information regarding how well MySQL is actually performing by checking the values of system variables.

In the first part of this chapter, we'll look at the commands you need to read configuration and system variables, which ones are likely to be most useful to you (and why), and how to change them when necessary. We'll also take a very brief look at some freely available tools that can help you monitor your server's performance and make changes in its configuration, including mytop (a top clone written in Perl), WinMySqlAdmin, phpMyAdmin, and the new MySQL Administrator, available from MySQL AB. MySQL Administrator promises to become a standard and valuable part of every MySQL database administrator's toolkit.

We'll also look at caching of tables, keys, and queries. MySQL's caches, when used properly, can save a lot of memory and processing overhead. They can speed up your applications considerably by cutting down on the number of times that the server must read and/or write to disk instead of RAM. The query cache, new in MySQL 4.0, is a major resource for improving efficiency. The query cache can have dramatic effects on the speed of frequently repeated queries on tables that are not updated often, particularly if those queries yield large resultsets.

It's also true that the efficiency of your MySQL application is going to be no better than that of your queries. The cardinal rule here is: Don't do what isn't necessary. So don't perform unneeded queries. Don't return columns and rows that aren't required by your application. Don't join tables that aren't relevant to the problem you're trying to solve. We'll try to point out the most common errors of these types and what you can do to correct them, or better yet, to avoid making them in the first place. We'll also try to point out some common issues with application logic that affect an application's efficiency, such as repeated queries and connections, unneeded calculations, and the matter of database interoperability layers.

Configuration Issues

In addition to optimizing MySQL databases and applications, you can do a lot toward optimizing the MySQL server itself by way of various configuration settings. The first step is to read the configuration and system variables. Once you've done this, you can take appropriate action if these variables indicate performance could be improved. This action might be one or more of the following:

• Changing a value in the my.cnf (or my.ini) configuration file
• Making changes in the design of one or more tables, or adding or modifying table indexes
• Rewriting the queries that are being used by the application
• Upgrading the server hardware or changing the network configuration

In this section, we'll concentrate on reading configuration and system variables, and changing configuration settings. Later in this chapter, we'll look at some of the other possible solutions.