Benchmarking Your Queries


In any kind of programming optimization problem, benchmarking is useful. By benchmarking, we mean specifically timing how long your queries take. This is best done by running a query many times and seeing how long it takes on average. A single execution of the query will be subject to load issues, so it may give unpredictable results. You will also find that the second time you run a query, it should be quicker because the query is cached.

You can, of course, use external scripts or programs to run queries multiple times. For an example, download the source distribution of MySQL and look at the benchmarking code that is included in the sql-bench directory.

You can also test the speed of evaluation of any expression (including a query) in MySQL by using the built-in BENCHMARK() function. For example:

 
 select benchmark(1000000, 6*9); 

This will produce something similar to the following results:

 
 +-------------------------+  benchmark(1000000, 6*9)  +-------------------------+                        0  +-------------------------+ 1 row in set (0.25 sec) 

The function takes two parameters: the number of times to evaluate the expression (in this case, one million) and the expression we want to evaluate (in this case, six times nine).

We do not really want to see the result of the SELECT query. The BENCHMARK() function always returns zero. What we are interested here is the time the query took to execute. In this sample output, you can see that evaluating 6x9 one million times on my system took a quarter of a second.

You can also pass BENCHMARK() a query, for example:

 
 select benchmark(10000000,  'select employee.name, department.name  from employee, department  where employee.departmentID=department.departmentID'); 


MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net