<div id="docs-body">
 <div class="chapter">
  <div class="titlepage">
   <div>
    <div>
     <h1 class="title">
      <a name="optimization">
      </a>
      Chapter 10 Optimization
     </h1>
    </div>
   </div>
  </div>
  <div class="toc">
   <p>
    <b>
     Table of Contents
    </b>
   </p>
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="optimize-overview.html">
       10.1 Optimization Overview
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="statement-optimization.html">
       10.2 Optimizing SQL Statements
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="select-optimization.html">
         10.2.1 Optimizing SELECT Statements
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="subquery-optimization.html">
         10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table
Expressions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="information-schema-optimization.html">
         10.2.3 Optimizing INFORMATION_SCHEMA Queries
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="performance-schema-optimization.html">
         10.2.4 Optimizing Performance Schema Queries
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="data-change-optimization.html">
         10.2.5 Optimizing Data Change Statements
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="permission-optimization.html">
         10.2.6 Optimizing Database Privileges
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="miscellaneous-optimization-tips.html">
         10.2.7 Other Optimization Tips
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimization-indexes.html">
       10.3 Optimization and Indexes
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="mysql-indexes.html">
         10.3.1 How MySQL Uses Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="primary-key-optimization.html">
         10.3.2 Primary Key Optimization
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="spatial-index-optimization.html">
         10.3.3 SPATIAL Index Optimization
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="foreign-key-optimization.html">
         10.3.4 Foreign Key Optimization
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="column-indexes.html">
         10.3.5 Column Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="multiple-column-indexes.html">
         10.3.6 Multiple-Column Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="verifying-index-usage.html">
         10.3.7 Verifying Index Usage
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="index-statistics.html">
         10.3.8 InnoDB and MyISAM Index Statistics Collection
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="index-btree-hash.html">
         10.3.9 Comparison of B-Tree and Hash Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="index-extensions.html">
         10.3.10 Use of Index Extensions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="generated-column-index-optimizations.html">
         10.3.11 Optimizer Use of Generated Column Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="invisible-indexes.html">
         10.3.12 Invisible Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="descending-indexes.html">
         10.3.13 Descending Indexes
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="timestamp-lookups.html">
         10.3.14 Indexed Lookups from TIMESTAMP Columns
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimizing-database-structure.html">
       10.4 Optimizing Database Structure
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="data-size.html">
         10.4.1 Optimizing Data Size
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimize-data-types.html">
         10.4.2 Optimizing MySQL Data Types
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimize-multi-tables.html">
         10.4.3 Optimizing for Many Tables
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="internal-temporary-tables.html">
         10.4.4 Internal Temporary Table Use in MySQL
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="database-count-limit.html">
         10.4.5 Limits on Number of Databases and Tables
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="table-size-limit.html">
         10.4.6 Limits on Table Size
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="column-count-limit.html">
         10.4.7 Limits on Table Column Count and Row Size
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimizing-innodb.html">
       10.5 Optimizing for InnoDB Tables
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-storage-layout.html">
         10.5.1 Optimizing Storage Layout for InnoDB Tables
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-transaction-management.html">
         10.5.2 Optimizing InnoDB Transaction Management
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="innodb-performance-ro-txn.html">
         10.5.3 Optimizing InnoDB Read-Only Transactions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-logging.html">
         10.5.4 Optimizing InnoDB Redo Logging
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-bulk-data-loading.html">
         10.5.5 Bulk Data Loading for InnoDB Tables
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-queries.html">
         10.5.6 Optimizing InnoDB Queries
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-ddl-operations.html">
         10.5.7 Optimizing InnoDB DDL Operations
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-diskio.html">
         10.5.8 Optimizing InnoDB Disk I/O
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-configuration-variables.html">
         10.5.9 Optimizing InnoDB Configuration Variables
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-innodb-many-tables.html">
         10.5.10 Optimizing InnoDB for Systems with Many Tables
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimizing-myisam.html">
       10.6 Optimizing for MyISAM Tables
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="optimizing-queries-myisam.html">
         10.6.1 Optimizing MyISAM Queries
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-myisam-bulk-data-loading.html">
         10.6.2 Bulk Data Loading for MyISAM Tables
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="repair-table-optimization.html">
         10.6.3 Optimizing REPAIR TABLE Statements
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimizing-memory-tables.html">
       10.7 Optimizing for MEMORY Tables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="execution-plan-information.html">
       10.8 Understanding the Query Execution Plan
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="using-explain.html">
         10.8.1 Optimizing Queries with EXPLAIN
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="explain-output.html">
         10.8.2 EXPLAIN Output Format
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="explain-extended.html">
         10.8.3 Extended EXPLAIN Output Format
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="explain-for-connection.html">
         10.8.4 Obtaining Execution Plan Information for a Named Connection
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="estimating-performance.html">
         10.8.5 Estimating Query Performance
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="controlling-optimizer.html">
       10.9 Controlling the Query Optimizer
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="controlling-query-plan-evaluation.html">
         10.9.1 Controlling Query Plan Evaluation
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="switchable-optimizations.html">
         10.9.2 Switchable Optimizations
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizer-hints.html">
         10.9.3 Optimizer Hints
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="index-hints.html">
         10.9.4 Index Hints
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="cost-model.html">
         10.9.5 The Optimizer Cost Model
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizer-statistics.html">
         10.9.6 Optimizer Statistics
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="buffering-caching.html">
       10.10 Buffering and Caching
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="innodb-buffer-pool-optimization.html">
         10.10.1 InnoDB Buffer Pool Optimization
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="myisam-key-cache.html">
         10.10.2 The MyISAM Key Cache
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="statement-caching.html">
         10.10.3 Caching of Prepared Statements and Stored Programs
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="locking-issues.html">
       10.11 Optimizing Locking Operations
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="internal-locking.html">
         10.11.1 Internal Locking Methods
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="table-locking.html">
         10.11.2 Table Locking Issues
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="concurrent-inserts.html">
         10.11.3 Concurrent Inserts
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="metadata-locking.html">
         10.11.4 Metadata Locking
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="external-locking.html">
         10.11.5 External Locking
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimizing-server.html">
       10.12 Optimizing the MySQL Server
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="disk-issues.html">
         10.12.1 Optimizing Disk I/O
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="symbolic-links.html">
         10.12.2 Using Symbolic Links
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizing-memory.html">
         10.12.3 Optimizing Memory Use
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimize-benchmarking.html">
       10.13 Measuring Performance (Benchmarking)
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="select-benchmarking.html">
         10.13.1 Measuring the Speed of Expressions and Functions
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="custom-benchmarks.html">
         10.13.2 Using Your Own Benchmarks
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="monitoring-performance-schema.html">
         10.13.3 Measuring Performance with performance_schema
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="thread-information.html">
       10.14 Examining Server Thread (Process) Information
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="processlist-access.html">
         10.14.1 Accessing the Process List
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="thread-commands.html">
         10.14.2 Thread Command Values
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="general-thread-states.html">
         10.14.3 General Thread States
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="source-thread-states.html">
         10.14.4 Replication Source Thread States
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="replica-io-thread-states.html">
         10.14.5 Replication I/O (Receiver) Thread States
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="replica-sql-thread-states.html">
         10.14.6 Replication SQL Thread States
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="replica-connection-thread-states.html">
         10.14.7 Replication Connection Thread States
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="mysql-cluster-thread-states.html">
         10.14.8 NDB Cluster Thread States
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="event-scheduler-thread-states.html">
         10.14.9 Event Scheduler Thread States
        </a>
       </span>
      </dt>
     </dl>
    </dd>
    <dt>
     <span class="section">
      <a href="optimizer-tracing.html">
       10.15 Tracing the Optimizer
      </a>
     </span>
    </dt>
    <dd>
     <dl>
      <dt>
       <span class="section">
        <a href="optimizer-tracing-typical-usage.html">
         10.15.1 Typical Usage
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="system-variables-controlling-tracing.html">
         10.15.2 System Variables Controlling Tracing
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="traceable-statements.html">
         10.15.3 Traceable Statements
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="tuning-trace-purging.html">
         10.15.4 Tuning Trace Purging
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="tracing-memory-usage.html">
         10.15.5 Tracing Memory Usage
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="privilege-checking.html">
         10.15.6 Privilege Checking
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="interaction-with-debug-option.html">
         10.15.7 Interaction with the --debug Option
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizer-trace-system-variable.html">
         10.15.8 The optimizer_trace System Variable
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="end-markers-in-json-system-variable.html">
         10.15.9 The end_markers_in_json System Variable
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizer-features-to-trace.html">
         10.15.10 Selecting Optimizer Features to Trace
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="trace-general-structure.html">
         10.15.11 Trace General Structure
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="tracing-example.html">
         10.15.12 Example
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="displaying-traces.html">
         10.15.13 Displaying Traces in Other Applications
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="preventing-use-of-optimizer-trace.html">
         10.15.14 Preventing the Use of Optimizer Trace
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizer-trace-testing.html">
         10.15.15 Testing Optimizer Trace
        </a>
       </span>
      </dt>
      <dt>
       <span class="section">
        <a href="optimizer-trace-implementation.html">
         10.15.16 Optimizer Trace Implementation
        </a>
       </span>
      </dt>
     </dl>
    </dd>
   </dl>
  </div>
  <a class="indexterm" name="idm46045230418656">
  </a>
  <a class="indexterm" name="idm46045230417584">
  </a>
  <a class="indexterm" name="idm46045230416512">
  </a>
  <p>
   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.
  </p>
 </div>
 <br/>
</div>
