MySQL Reference Manual

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 checks result 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)

This shows that MySQL can execute 1,000,000 + expressions in 0.32 second on a Pentium II 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.

EXPLAIN Syntax (Get Information About a SELECT)

    EXPLAIN tbl_name
or  EXPLAIN SELECT select_options

EXPLAIN tbl_name is a synonym for DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name.

When you precede a SELECT statement with the keyword EXPLAIN, MySQL explains how it would process the SELECT, providing information about how tables are joined and in which order.

With the help of EXPLAIN, you can see when you must add indexes to tables to get a faster SELECT that uses indexes to find the records. You can also see if the optimiser joins the tables in ...