7.2. Optimizing SELECT and Other Statements
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.
