8.3.1 Optimizing SELECT Statements

8.3.1.1 Speed of SELECT Statements
8.3.1.2 How MySQL Optimizes WHERE Clauses
8.3.1.3 Range Optimization
8.3.1.4 Index Merge Optimization
8.3.1.5 Engine Condition Pushdown Optimization
8.3.1.6 IS NULL Optimization
8.3.1.7 LEFT JOIN and RIGHT JOIN Optimization
8.3.1.8 Nested-Loop Join Algorithms
8.3.1.9 Nested Join Optimization
8.3.1.10 Outer Join Simplification
8.3.1.11 ORDER BY Optimization
8.3.1.12 GROUP BY Optimization
8.3.1.13 DISTINCT Optimization
8.3.1.14 Optimizing Subqueries with EXISTS Strategy
8.3.1.15 Optimizing LIMIT Queries
8.3.1.16 How to Avoid Table Scans

First, one factor affects all statements: The more complex your permissions setup, the more overhead you have. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you do not grant any table-level or column-level privileges, the server need not ever check the contents of the tables_priv and columns_priv tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high statement-processing load, it may be worth the time to use a simplified grant structure to reduce permission-checking overhead.

If your problem is with a specific MySQL expression or function, you can perform a timing test by invoking the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expression). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute. For example:

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.32 sec)

This result was obtained on a Pentium II 400MHz system. It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system.

All MySQL functions should be highly optimized, but there may be some exceptions. BENCHMARK() is an excellent tool for finding out if some function is a problem for your queries.