MySQL® Administrator's Bible

Chapter 18. Query Analysis and Index Tuning

IN THIS CHAPTER

• Analyzing queries using EXPLAIN

• Using EXPLAIN on non-SELECT statements

• Using other query analysis tools

• Optimizing queries

"The database is slow!"

A DBA will hear this over and over during his or her career. Although there are times when performance can be tuned best by changing server parameters, most of the time database slowness, it is because the:

• The query cache is not being utilized properly (see Chapter 12 for troubleshooting).

• The query contains subqueries (MySQL Server 5.1) or unoptimized subqueries (MySQL Server 6.0). See the companion website at www.wiley.com/go/mysqladminbible for information about how to avoid subqueries.

• The table contains a large amount of unnecessary data.

• The table is fragmented.

• The schema was not designed for the queries being run.

• The queries being run do not take into consideration the schema design.

• Tables have no indexes that are appropriate for the query.

The reason for the disparity between the schema and queries may be that a developer is not familiar with MySQL, a DBA made assumptions about database usage, or the schema and queries were in sync at one point, but the data and/or application has grown.

A database schema is always a work in progress; as long as different queries are being written or the data inside a schema is changing, it is necessary to reanalyze both the schema and queries.

The larger issues of benchmarking and profiling are covered in Chapter 17, "Measuring Performance"; these ...