MySQL 8.0 Reference Manual 8.2.7 Other Optimization Tips ........................................................................................ 1714 8.3 Optimization and Indexes ................................................................................................ 1715 8.3.1 How MySQL Uses Indexes ................................................................................... 1715 8.3.2 Primary Key Optimization ..................................................................................... 1716 8.3.3 SPATIAL Index Optimization ................................................................................. 1716 8.3.4 Foreign Key Optimization ..................................................................................... 1717 8.3.5 Column Indexes ................................................................................................... 1717 8.3.6 Multiple-Column Indexes ...................................................................................... 1719 8.3.7 Verifying Index Usage .......................................................................................... 1720 8.3.8 InnoDB and MyISAM Index Statistics Collection ..................................................... 1720 8.3.9 Comparison of B-Tree and Hash Indexes .............................................................. 1722 8.3.10 Use of Index Extensions ..................................................................................... 1723 8.3.11 Optimizer Use of Generated Column Indexes ...................................................... 1725 8.3.12 Invisible Indexes ................................................................................................. 1727 8.3.13 Descending Indexes ........................................................................................... 1729 8.3.14 Indexed Lookups from TIMESTAMP Columns ...................................................... 1730 8.4 Optimizing Database Structure ........................................................................................ 1732 8.4.1 Optimizing Data Size ............................................................................................ 1733 8.4.2 Optimizing MySQL Data Types ............................................................................. 1735 8.4.3 Optimizing for Many Tables .................................................................................. 1736 8.4.4 Internal Temporary Table Use in MySQL ............................................................... 1737 8.4.5 Limits on Number of Databases and Tables .......................................................... 1741 8.4.6 Limits on Table Size ............................................................................................ 1741 8.4.7 Limits on Table Column Count and Row Size ........................................................ 1743 8.5 Optimizing for InnoDB Tables .......................................................................................... 1745 8.5.1 Optimizing Storage Layout for InnoDB Tables ........................................................ 1745 8.5.2 Optimizing InnoDB Transaction Management ........................................................ 1746 8.5.3 Optimizing InnoDB Read-Only Transactions .......................................................... 1747 8.5.4 Optimizing InnoDB Redo Logging ......................................................................... 1748 8.5.5 Bulk Data Loading for InnoDB Tables ................................................................... 1749 8.5.6 Optimizing InnoDB Queries .................................................................................. 1751 8.5.7 Optimizing InnoDB DDL Operations ...................................................................... 1751 8.5.8 Optimizing InnoDB Disk I/O .................................................................................. 1751 8.5.9 Optimizing InnoDB Configuration Variables ............................................................ 1755 8.5.10 Optimizing InnoDB for Systems with Many Tables ................................................ 1757 8.6 Optimizing for MyISAM Tables ........................................................................................ 1757 8.6.1 Optimizing MyISAM Queries ................................................................................. 1757 8.6.2 Bulk Data Loading for MyISAM Tables .................................................................. 1758 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 ix