Professional Website Performance: Optimizing the Front-End and Back-End

Chapter 8

Tuning MySQL

WHAT’S IN THIS CHAPTER?

• Getting to know the myriad of performance tuning options available in MySQL
• Writing efficient SQL queries, and spotting bottlenecks in existing queries
• Getting the most out of the MySQL query cache
• Discovering the latest advances in MyISAM and InnoDB

As a relative newcomer on the scene (with the first release in 1995), MySQL has quickly become the de facto relational database management system (RDBMS) for Linux, and one of the most popular for Windows and UNIX. Despite lacking some of the more advanced features found in other RDBMSs (this is gradually changing, but things like stored procedures and transactions are still relatively new additions), MySQL is lightweight, and has a good reputation for speed, making it an excellent choice for general-purpose web development.

Despite this reputation for speed, there is typically still a huge scope for optimization, both in the configuration of the MySQL server, and in queries themselves. This chapter familiarizes you with the performance aspects of MySQL.

This chapter begins by examining how the main storage engines differ, and the impact that this has on performance. After looking at general MySQL tuning issues, this chapter discusses tuning of the two most popular engines: MyISAM and InnoDB. After a discussion of MySQL’s query caching capabilities, this chapter concludes with tips on how to write more efficient SQL queries, and how to debug slow-running queries.

NOTE The terms cache ...