Chapter 8 Optimization

Table of Contents

8.1 Optimization Overview
8.1.1 MySQL Design Limitations and Tradeoffs
8.1.2 Designing Applications for Portability
8.1.3 The MySQL Benchmark Suite
8.1.4 Using Your Own Benchmarks
8.2 Optimizing SQL Statements
8.2.1 Optimizing SELECT Statements
8.2.2 Optimizing Non-SELECT Statements
8.2.3 Other Optimization Tips
8.3 Optimization and Indexes
8.3.1 How MySQL Uses Indexes
8.3.2 Column Indexes
8.3.3 Multiple-Column Indexes
8.3.4 Verifying Index Usage
8.3.5 MyISAM Index Statistics Collection
8.3.6 Comparison of B-Tree and Hash Indexes
8.4 Optimizing Database Structure
8.4.1 Optimizing Data Size
8.4.2 Using PROCEDURE ANALYSE
8.4.3 How MySQL Opens and Closes Tables
8.4.4 Disadvantages of Creating Many Tables in the Same Database
8.4.5 How MySQL Uses Internal Temporary Tables
8.5 Understanding the Query Execution Plan
8.5.1 Optimizing Queries with EXPLAIN
8.5.2 EXPLAIN Output Format
8.5.3 EXPLAIN EXTENDED Output Format
8.5.4 Estimating Query Performance
8.6 Controlling the Query Optimizer
8.6.1 Controlling Query Plan Evaluation
8.6.2 Index Hints
8.7 Buffering and Caching
8.7.1 The MyISAM Key Cache
8.7.2 The InnoDB Buffer Pool
8.7.3 The MySQL Query Cache
8.8 Optimizing Locking Operations
8.8.1 Internal Locking Methods
8.8.2 Table Locking Issues
8.8.3 Concurrent Inserts
8.8.4 External Locking
8.9 Optimizing the MySQL Server
8.9.1 System Factors and Startup Parameter Tuning
8.9.2 Tuning Server Parameters
8.9.3 How MySQL Uses Threads for Client Connections
8.9.4 How MySQL Uses Memory
8.9.5 Disk Issues
8.9.6 Using Symbolic Links
8.9.7 Enabling Large Page Support
8.9.8 DNS Lookup Optimization and the Host Cache
8.10 Examining Thread Information
8.10.1 Thread Command Values
8.10.2 General Thread States
8.10.3 Delayed-Insert Thread States
8.10.4 Query Cache Thread States
8.10.5 Replication Master Thread States
8.10.6 Replication Slave I/O Thread States
8.10.7 Replication Slave SQL Thread States
8.10.8 Replication Slave Connection Thread States
8.10.9 MySQL Cluster Thread States

This chapter explains how to optimize MySQL performance and provides examples. Optimization involves configuring, tuning, and measuring performance, at several levels. Depending on your job role (developer, DBA, or a combination of both), you might optimize at the level of individual SQL statements, entire applications, a single database server, or multiple networked database servers. Sometimes you can be proactive and plan in advance for performance, while other times you might troubleshoot a configuration or code issue after a problem occurs. Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.