This documentation is for an older version. If you're using the most current version, select the documentation for that version with the version switch in the upper right corner of the online documentation, or by downloading a newer PDF or EPUB file.

8.2.1 Optimizing SELECT Statements Speed of SELECT Statements How MySQL Optimizes WHERE Clauses Range Optimization Index Merge Optimization Engine Condition Pushdown Optimization IS NULL Optimization LEFT JOIN and RIGHT JOIN Optimization Nested-Loop Join Algorithms Nested Join Optimization Outer Join Simplification ORDER BY Optimization GROUP BY Optimization DISTINCT Optimization Optimizing Subqueries with EXISTS Strategy LIMIT Query Optimization Row Constructor Expression Optimization How to Avoid Full Table Scans

Queries, in the form of SELECT statements, perform all the lookup operations in the database. Tuning these statements is a top priority, whether to achieve sub-second response times for dynamic web pages, or to chop hours off the time to generate huge overnight reports.

Besides SELECT statements, the tuning techniques for queries also apply to constructs such as CREATE TABLE...AS SELECT, INSERT INTO...SELECT, and WHERE clauses in DELETE statements. Those statements have additional performance considerations because they combine write operations with the read-oriented query operations.