8.2.1 Optimizing SELECT Statements

8.2.1.1 Speed of SELECT Statements
8.2.1.2 How MySQL Optimizes WHERE Clauses
8.2.1.3 Range Optimization
8.2.1.4 Index Merge Optimization
8.2.1.5 Engine Condition Pushdown Optimization
8.2.1.6 IS NULL Optimization
8.2.1.7 LEFT JOIN and RIGHT JOIN Optimization
8.2.1.8 Nested-Loop Join Algorithms
8.2.1.9 Nested Join Optimization
8.2.1.10 Outer Join Simplification
8.2.1.11 ORDER BY Optimization
8.2.1.12 GROUP BY Optimization
8.2.1.13 DISTINCT Optimization
8.2.1.14 Optimizing Subqueries with EXISTS Strategy
8.2.1.15 Optimizing LIMIT Queries
8.2.1.16 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.

The main considerations for optimizing queries are: