MySQL Performance Optimization

Cache The myisamchk Utility Index and Table Compression InnoDB Indexing Features Index Structure and Storage Buffer Pool Memory Pool Adaptive Hash Index Automatic Foreign Key Index Generation Indexing Scenarios PRIMARY KEY Filter Columns Join Columns Index Cardinality Character Versus Numeric Indexes Multicolumn Indexes Partial Indexes Ascending Versus Descending Indexes Storing Tables in Column-Sorted Order III. OPTIMIZING APPLICATION CODE. 8. Advanced SQL Tips. Improving Searches Leveraging Internal Engine Caches Controlling Data Retrieval Reducing Security Costs Off-loading Processing Work Boosting Join Performance Substring Searches Improving Temporary Table Performance Managing View Performance Subqueries Using Math Within SQL UNIONs Sorting HANDLER Improving Data Modification Improving INSERT Improving DELETE 9. Developing High-Speed Applications. Understanding Locking General Locking Overview InnoDB Locking Overview Optimal Transactions Key Transaction Terms and Concepts Cost of Transactions Transaction Performance Suggestions Stored Procedures and Functions Difference Between Stored Procedures and Functions When to Use Stored Procedures or Functions When Not to Use Stored Procedures or Functions Stored Procedure Optimization Triggers Current Trigger Limitations When to Use Triggers When Not to Use Triggers Writing Efficient Application Code General Suggestions IV. OPTIMIZING AND TUNING THE MYSQL ENGINE. 10. General Server Performance Parameters and Tuning. Server Settings Scope Connectivity Creating a Connection Managing a Connection Memory Management Locking mysqld in Memory Thread Memory Settings MEMORY Tables Caching Application Control Resources Optimizer Control Concurrency User Resource Control 11. MyISAM Performance Enhancement. Optimal Data Storage Table Reporting and Control Table Maintenance and Repair Controlling Sorting Resources MyISAM and Memory The Key Cache Speeding MyISAM Operations Loading Information Improving FULLTEXT Searches Concurrency Considerations 12. InnoDB Performance Enhancement. InnoDB Storage Architecture and I/O Indexes Pages Extents and Segments Tablespaces Built-in Performance Enhancers Data Storage Log Files InnoDB and Memory Buffer Pool InnoDB Operational Control Thread Performance Improving Concurrency Improving Large-Scale Operations Speeding Up Shutdown 13. Improving Disk Speed. General Suggestions Deploying the Fastest Possible Disk Drives Configuring Memory Caches Correctly Implementing a RAID Strategy Distributing Temporary Directories MyISAM Suggestions Symbolic Links Table Compression InnoDB Suggestions Choosing the Right autoextend Setting Using Raw Devices Alternative Disk Writing Methods Table Defragmentation Operating System--Specific Disk Suggestions Linux/Unix Suggestions Windows Suggestions 14. Operating System, Web Server, and Connectivity Tuning. Operating System Considerations Assumptions Linux/Unix Windows Web Server Performance Considerations Choosing the Right Server Topology General PHP Suggestions Apache/PHP Suggestions Internet Information Services (IIS)/PHP Tips Connectivity Tuning Protocol Choices Costs of Hostname Resolution Costs of Creating and Destroying Connections Costs of SSL 15. Improving Import and Export Operations. Speeding Data Exports The Importance of Regular Exports Avoiding Excessive Memory Consumption Concurrency and Exports Retrieving Subsets of Information Copying Table Structures Without Data Delaying Index Re-creation--MyISAM Delaying Index Re-creation--InnoDB Preparing for Data Reload Accelerating Data Loading Managing Concurrency Handling Errors Engine-Specific Tips V. DISTRIBUTED COMPUTING. 16. Optimal Replication. Introduction How Does Replication Work? Is Replication Right for You? Creating a Replication Strategy High-Performance Replication Network Considerations Master Server Performance Considerations Implementing Best Tuning Practices Managing Slave Resources Slave Server Performance Considerations 17. Optimal Clustering. Introduction How Does Clustering Work? Nodes Shared-Nothing Cluster Clients Storage Engine Transporter Data Distribution Checkpoints Is Clustering Right for You? Clustering Versus Replication Creating a Clustering Strategy Choosing the Right Version Cluster Topology Configuring MySQL Cluster for High Performance General Clustering Best Practices Network Considerations Transports Management Node Considerations SQL Node Considerations Data Node Considerations VI. CASE STUDIES. 18. Case Study: High-Hat Delivers! Problem Queries Package Status Lookup Shipping Option Lookup Random Transaction Bottlenecks Diagnosis Solution Implementing These Solutions 19. Case Study: Friends Fly Free-for-All--A Promotion Gone Wrong. Server Availability Diagnosis Solution Application and Transaction Issues Diagnosis Solution 20. Case Study 3: Practice Makes Perfect. Data Importing Diagnosis Solution Clustering Diagnosis Solution Stored Procedures Diagnosis Solution Index.