5.2 Optimising SELECTs and Other Queries

First, one thing that affects all queries: The more complex permission system setup you have, the more overhead you get.

If you do not have any GRANT statements done, MySQL will optimise the permission checking somewhat. So if you have a very high volume it may be worth the time to avoid grants. Otherwise, more permission check results in a larger overhead.

If your problem is with some explicit MySQL function, you can always time this in the MySQL client:

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

The above shows that MySQL can execute 1,000,000 + expressions in 0.32 seconds on a PentiumII 400MHz.

All MySQL functions should be very optimised, but there may be some exceptions, and the BENCHMARK(loop_count,expression) is a great tool to find out if this is a problem with your query.

Subsections

• 5.2.1 EXPLAIN Syntax (Get Information About a SELECT)
• 5.2.2 Estimating Query Performance
• 5.2.3 Speed of SELECT Queries
• 5.2.4 How MySQL Optimises WHERE Clauses
• 5.2.5 How MySQL Optimises IS NULL
• 5.2.6 How MySQL Optimises DISTINCT
• 5.2.7 How MySQL Optimises LEFT JOIN and RIGHT JOIN
• 5.2.8 How MySQL Optimises ORDER BY
• 5.2.9 How MySQL Optimises LIMIT
• 5.2.10 Speed of INSERT Queries
• 5.2.11 Speed of UPDATE Queries
• 5.2.12 Speed of DELETE Queries
• 5.2.13 Other Optimisation Tips

User Comments

Add your own comment.