Day 18. Optimizing Performance

By now you should have learned how to design and build MySQL database applications that serve a variety of different requirements. However, so far you have spent only a little time here and there considering database performance.

In today's lesson, you will focus on attaining the goal of high performance. You will look at the various things that influence it, so that at the end of the lesson you have some techniques for running MySQL with far greater efficiency, even in high-load, performance-critical situations.

In particular, you will learn:

• The principal factors that influence performance

• How to improve your table designs to speed up database access

• How to write applications that run faster

• How to use MySQL's performance commands, EXPLAIN, BENCHMARK, and PROCEDURE ANALYSE

• How to configure your MySQL server, your operating system, and your hardware for optimal performance.

This lesson is not intended to be a study of MySQL's query optimizer. The optimizer is an essential part of the server; it studies each query it is sent and tries to find the best way to execute it. We will look at the effect the optimizer has in a number of places throughout the lesson, but to keep things concise and practical, we will not endeavor to explain the thinking behind the optimizer. You should study the MySQL Reference Manual for more detail on this.