Running a query once and checking its execution speed is not benchmarking, I'm sorry. Thanks to the influence of the query cache, and other similar buffers and caches, it is very possible that a query that runs slow the first time it is called will be lightning fast the second and subsequent times, whereas other queries may always be slow.
The best way to test the performance is by using the benchmark() function in MySQL to test expressions you are interested in, and it takes two parameters: the number of times to run the test, and the expression to evaluate. Running the benchmark() function evaluates the expression in parameter two the number of times specified in parameter one, then returns 0. Of course, if you are using the MySQL client, you will also see the length of time the query took to return, which is what we're interested in. For example:
mysql> select benchmark(100000000, 1 = 1);
+-----------------------------+
| benchmark(100000000, 1 = 1) |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (8.18 sec)
mysql> select benchmark(100000000, 'foo' = 'foo');
+-------------------------------------+
| benchmark(100000000, 'foo' = 'foo') |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (15.09 sec)
In those two queries you can see I am comparing the performance of number comparison against string comparison by evaluating each expression 100,000,000 times. There are two key things to note - firstly, you always get 0 as the return value irrespective of what you evaluated. Secondly, the elapsed time count ("8.18 sec" and "15.09 sec" respectively) is the number of seconds that have passed on the client, and does not necessarily reflect the number of seconds the server spent processing the query. For example, in the situation above, it is possible that comparing 'foo' = 'foo' is faster than 1 = 1, but that while the string compare benchmark was taking place another, higher priority query came in and took up all the CPU time. The only way to make sure the results are reliable is to execute the benchmark query several times.
To give you an idea of what kind of penalty to expect if you do ask MySQL to compare across data types, take a look at this next benchmark run:
mysql> select benchmark(100000000, 1 = "1");
+-------------------------------+
| benchmark(100000000, 1 = "1") |
+-------------------------------+
| 0 |
+-------------------------------+
1 row in set (2 min 29.19 sec)
Yes, that does say two minutes and twenty-nine seconds, so be warned.
To benchmark more complex situations, there is no harm in reproducing the benchmark() function in PHP - write a loop that runs a block of code 1000 times and use time() (or, for more accuracy, microtime() ) to check performance.
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Know MySQL's strengths >>
Previous chapter: Don't rely on automatic type conversion
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.