Skip to Main Content
The world's most popular open source database
Contact MySQL
|
Login
|
Register
MySQL.com
Downloads
Documentation
Developer Zone
Developer Zone
Downloads
MySQL.com
Documentation
MySQL Server
MySQL Enterprise
Workbench
InnoDB Cluster
MySQL NDB Cluster
Connectors
More
MySQL.com
Downloads
Developer Zone
Section Menu:
Documentation Home
MySQL 8.4 Reference Manual
Preface and Legal Notices
General Information
Installing MySQL
Upgrading MySQL
Downgrading MySQL
Tutorial
MySQL Programs
MySQL Server Administration
Security
Backup and Recovery
Optimization
Optimization Overview
Optimizing SQL Statements
Optimizing SELECT Statements
WHERE Clause Optimization
Range Optimization
Index Merge Optimization
Hash Join Optimization
Engine Condition Pushdown Optimization
Index Condition Pushdown Optimization
Nested-Loop Join Algorithms
Nested Join Optimization
Outer Join Optimization
Outer Join Simplification
Multi-Range Read Optimization
Block Nested-Loop and Batched Key Access Joins
Condition Filtering
Constant-Folding Optimization
IS NULL Optimization
ORDER BY Optimization
GROUP BY Optimization
DISTINCT Optimization
LIMIT Query Optimization
Function Call Optimization
Window Function Optimization
Row Constructor Expression Optimization
Avoiding Full Table Scans
Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions
Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations
Optimizing Subqueries with Materialization
Optimizing Subqueries with the EXISTS Strategy
Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization
Derived Condition Pushdown Optimization
Optimizing INFORMATION_SCHEMA Queries
Optimizing Performance Schema Queries
Optimizing Data Change Statements
Optimizing INSERT Statements
Optimizing UPDATE Statements
Optimizing DELETE Statements
Optimizing Database Privileges
Other Optimization Tips
Optimization and Indexes
How MySQL Uses Indexes
Primary Key Optimization
SPATIAL Index Optimization
Foreign Key Optimization
Column Indexes
Multiple-Column Indexes
Verifying Index Usage
InnoDB and MyISAM Index Statistics Collection
Comparison of B-Tree and Hash Indexes
Use of Index Extensions
Optimizer Use of Generated Column Indexes
Invisible Indexes
Descending Indexes
Indexed Lookups from TIMESTAMP Columns
Optimizing Database Structure
Optimizing Data Size
Optimizing MySQL Data Types
Optimizing for Numeric Data
Optimizing for Character and String Types
Optimizing for BLOB Types
Optimizing for Many Tables
How MySQL Opens and Closes Tables
Disadvantages of Creating Many Tables in the Same Database
Internal Temporary Table Use in MySQL
Limits on Number of Databases and Tables
Limits on Table Size
Limits on Table Column Count and Row Size
Optimizing for InnoDB Tables
Optimizing Storage Layout for InnoDB Tables
Optimizing InnoDB Transaction Management
Optimizing InnoDB Read-Only Transactions
Optimizing InnoDB Redo Logging
Bulk Data Loading for InnoDB Tables
Optimizing InnoDB Queries
Optimizing InnoDB DDL Operations
Optimizing InnoDB Disk I/O
Optimizing InnoDB Configuration Variables
Optimizing InnoDB for Systems with Many Tables
Optimizing for MyISAM Tables
Optimizing MyISAM Queries
Bulk Data Loading for MyISAM Tables
Optimizing REPAIR TABLE Statements
Optimizing for MEMORY Tables
Understanding the Query Execution Plan
Optimizing Queries with EXPLAIN
EXPLAIN Output Format
Extended EXPLAIN Output Format
Obtaining Execution Plan Information for a Named Connection
Estimating Query Performance
Controlling the Query Optimizer
Controlling Query Plan Evaluation
Switchable Optimizations
Optimizer Hints
Index Hints
The Optimizer Cost Model
Optimizer Statistics
Buffering and Caching
InnoDB Buffer Pool Optimization
The MyISAM Key Cache
Shared Key Cache Access
Multiple Key Caches
Midpoint Insertion Strategy
Index Preloading
Key Cache Block Size
Restructuring a Key Cache
Caching of Prepared Statements and Stored Programs
Optimizing Locking Operations
Internal Locking Methods
Table Locking Issues
Concurrent Inserts
Metadata Locking
External Locking
Optimizing the MySQL Server
Optimizing Disk I/O
Using Symbolic Links
Using Symbolic Links for Databases on Unix
Using Symbolic Links for MyISAM Tables on Unix
Using Symbolic Links for Databases on Windows
Optimizing Memory Use
How MySQL Uses Memory
Monitoring MySQL Memory Usage
Enabling Large Page Support
Measuring Performance (Benchmarking)
Measuring the Speed of Expressions and Functions
Using Your Own Benchmarks
Measuring Performance with performance_schema
Examining Server Thread (Process) Information
Accessing the Process List
Thread Command Values
General Thread States
Replication Source Thread States
Replication I/O (Receiver) Thread States
Replication SQL Thread States
Replication Connection Thread States
NDB Cluster Thread States
Event Scheduler Thread States
Tracing the Optimizer
Typical Usage
System Variables Controlling Tracing
Traceable Statements
Tuning Trace Purging
Tracing Memory Usage
Privilege Checking
Interaction with the --debug Option
The optimizer_trace System Variable
The end_markers_in_json System Variable
Selecting Optimizer Features to Trace
Trace General Structure
Example
Displaying Traces in Other Applications
Preventing the Use of Optimizer Trace
Testing Optimizer Trace
Optimizer Trace Implementation
Language Structure
Character Sets, Collations, Unicode
Data Types
Functions and Operators
SQL Statements
MySQL Data Dictionary
The InnoDB Storage Engine
Alternative Storage Engines
Replication
Group Replication
MySQL Shell
Using MySQL as a Document Store
InnoDB Cluster
InnoDB ReplicaSet
MySQL NDB Cluster 8.4
Partitioning
Stored Objects
INFORMATION_SCHEMA Tables
MySQL Performance Schema
MySQL sys Schema
Connectors and APIs
MySQL Enterprise Edition
MySQL Workbench
MySQL on OCI Marketplace
Telemetry
MySQL 8.4 Frequently Asked Questions
Error Messages and Common Problems
Indexes
MySQL Glossary
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr)
- 40.0Mb
PDF (A4)
- 40.1Mb
Man Pages (TGZ)
- 259.1Kb
Man Pages (Zip)
- 366.2Kb
Info (Gzip)
- 4.0Mb
Info (Zip)
- 4.0Mb
version 8.4
9.1
9.0
8.0
5.7
8.0 Japanese
MySQL 8.4 Reference Manual
/
Optimization
/ Buffering and Caching
10.10 Buffering and Caching
10.10.1 InnoDB Buffer Pool Optimization
10.10.2 The MyISAM Key Cache
10.10.3 Caching of Prepared Statements and Stored Programs
MySQL uses several strategies that cache information in memory buffers to increase performance.
PREV
HOME
UP
NEXT
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr)
- 40.0Mb
PDF (A4)
- 40.1Mb
Man Pages (TGZ)
- 259.1Kb
Man Pages (Zip)
- 366.2Kb
Info (Gzip)
- 4.0Mb
Info (Zip)
- 4.0Mb