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 Obtaining Query Execution Plan Information
8.2.1 Optimizing Queries with EXPLAIN
8.2.2 EXPLAIN Output Format
8.2.3 EXPLAIN EXTENDED Output Format
8.2.4 Estimating Query Performance
8.3 Optimizing SQL Statements
8.3.1 Optimizing SELECT Statements
8.3.2 Optimizing Non-SELECT Statements
8.3.3 INFORMATION_SCHEMA Optimization
8.3.4 Other Optimization Tips
8.4 Controlling the Query Optimizer
8.4.1 Controlling Query Plan Evaluation
8.4.2 Controlling Switchable Optimizations
8.5 Optimization and Indexes
8.5.1 How MySQL Uses Indexes
8.5.2 Column Indexes
8.5.3 Multiple-Column Indexes
8.5.4 InnoDB and MyISAM Index Statistics Collection
8.5.5 Comparison of B-Tree and Hash Indexes
8.6 Buffering and Caching
8.6.1 The MyISAM Key Cache
8.6.2 The InnoDB Buffer Pool
8.6.3 The MySQL Query Cache
8.7 Locking Issues
8.7.1 Internal Locking Methods
8.7.2 Table Locking Issues
8.7.3 Concurrent Inserts
8.7.4 External Locking
8.8 Optimizing Database Structure
8.8.1 Make Your Data as Small as Possible
8.8.2 Using PROCEDURE ANALYSE
8.8.3 How MySQL Opens and Closes Tables
8.8.4 Disadvantages of Creating Many Tables in the Same Database
8.8.5 How MySQL Uses Internal Temporary Tables
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
8.10.10 Event Scheduler Thread States

This chapter explains different ways to optimize MySQL and provides examples. Optimization is a complex task because ultimately it requires understanding of the entire system to be optimized. Although it may be possible to perform some local optimizations with little knowledge of your system or application, the more optimal you want your system to become, the more you must know about it.