8.6.3 Optimizing REPAIR TABLE Statements ........................................................................ 1760 8.7 Optimizing for MEMORY Tables .............................................................................................. 1761 8.8 Understanding the Query Execution Plan ................................................................................. 1761 8.8.1 Optimizing Queries with EXPLAIN ................................................................................ 1761 8.8.2 EXPLAIN Output Format .............................................................................................. 1762 8.8.3 Extended EXPLAIN Output Format ............................................................................... 1776 8.8.4 Obtaining Execution Plan Information for a Named Connection ....................................... 1779 8.8.5 Estimating Query Performance ..................................................................................... 1779 8.9 Controlling the Query Optimizer ............................................................................................... 1780 8.9.1 Controlling Query Plan Evaluation ................................................................................. 1780 8.9.2 Switchable Optimizations .............................................................................................. 1781 8.9.3 Optimizer Hints ............................................................................................................ 1791 8.9.4 Index Hints .................................................................................................................. 1806 8.9.5 The Optimizer Cost Model ............................................................................................ 1809 8.9.6 Optimizer Statistics ....................................................................................................... 1812 8.10 Buffering and Caching ........................................................................................................... 1815 8.10.1 InnoDB Buffer Pool Optimization ................................................................................. 1815 8.10.2 The MyISAM Key Cache ............................................................................................ 1816 8.10.3 Caching of Prepared Statements and Stored Programs ................................................ 1820 8.11 Optimizing Locking Operations .............................................................................................. 1822 8.11.1 Internal Locking Methods ............................................................................................ 1822 8.11.2 Table Locking Issues .................................................................................................. 1824 8.11.3 Concurrent Inserts ...................................................................................................... 1826 8.11.4 Metadata Locking ....................................................................................................... 1826 8.11.5 External Locking ......................................................................................................... 1830 8.12 Optimizing the MySQL Server ............................................................................................... 1831 8.12.1 Optimizing Disk I/O .................................................................................................... 1831 8.12.2 Using Symbolic Links ................................................................................................. 1832 8.12.3 Optimizing Memory Use ............................................................................................. 1835 8.13 Measuring Performance (Benchmarking) ................................................................................ 1841 8.13.1 Measuring the Speed of Expressions and Functions .................................................... 1842 8.13.2 Using Your Own Benchmarks ..................................................................................... 1842 8.13.3 Measuring Performance with performance_schema ...................................................... 1843 8.14 Examining Server Thread (Process) Information ..................................................................... 1843 8.14.1 Accessing the Process List ......................................................................................... 1843 8.14.2 Thread Command Values ........................................................................................... 1845 8.14.3 General Thread States ............................................................................................... 1847 8.14.4 Replication Source Thread States ............................................................................... 1854 8.14.5 Replication I/O Thread States ..................................................................................... 1854 8.14.6 Replication SQL Thread States ................................................................................... 1855 8.14.7 Replication Connection Thread States ......................................................................... 1856 8.14.8 NDB Cluster Thread States ........................................................................................ 1857 8.14.9 Event Scheduler Thread States .................................................................................. 1857 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. 1638