Chapter 8 Optimization Table of Contents 8.1 Optimization Overview ............................................................................................................. 1639 8.2 Optimizing SQL Statements .................................................................................................... 1640 8.2.1 Optimizing SELECT Statements ................................................................................... 1640 8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions .......................................................................................................................... 1694 8.2.3 Optimizing INFORMATION_SCHEMA Queries .............................................................. 1708 8.2.4 Optimizing Performance Schema Queries ..................................................................... 1711 8.2.5 Optimizing Data Change Statements ............................................................................. 1712 8.2.6 Optimizing Database Privileges ..................................................................................... 1714 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 1637