6 Ways To Benchmark & Measure MYSQL Query Time

Welcome to a short tutorial on how to measure the time taken to execute a query in MYSQL. So just how long did a query take to run in MYSQL? Which query is better and faster? There are actually quite a number of ways to measure and benchmark queries in MYSQL:

    1. The simplest way is to run the query in the command line, MYSQL is set to show the query execution time by default.
    2. Similarly, with tools like MYSQL Workbench and PHPMyAdmin, the execution time will be shown after running the query.
    3. Use MYSQL query profiling.
      • SET profiling = 1;
      • Run your statements.
      • SHOW PROFILES;
    4. Use the BENCHMARK(LOOP, STATEMENT) function.
    5. Manually write a test script to benchmark the performance of the query.
    6. Lastly, use a benchmarking tool such as Sysbench.

That covers the quick basics, read on for the detailed examples!

ⓘ I have included a zip file with all the example code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes MYSQL Query Time Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

MEASURING MYSQL QUERY TIME

All right, let us now get into the various ways and examples of getting the query execution time in MYSQL.

 

1) RUN QUERY IN MYSQL COMMAND LINE

This is probably the simplest way to check a query execution time, without having to install anything else. Just fire up the command line (or terminal), run MYSQL, and execute the SQL query. The time taken is set to be shown by default.

 

2) USING MYSQL TOOLS & MANAGERS

If you are using the “common MYSQL tools” such as MYSQL Workbench and PHPMyAdmin, both of them are also set to show the query execution time by default. No sweat.

 

 

3) MYSQL QUERY PROFILING

Now, some of you code ninjas may be thinking “what if I want to check for multiple queries”? Introducing MYSQL query profiling.

  • Turn on the “profiling mode” with SET profiling = 1.
  • Run all your SQL queries and statements as usual.
  • Then SHOW PROFILES to see how long each query takes.
  • You can also use SHOW PROFILE and SHOW PROFILE FOR QUERY (Query ID) to see more details for the queries.

 

4) BENCHMARK FUNCTION

Again, some of you sharp code ninjas may be thinking “running the query one time is not conclusive”. So yes, there is also a native MYSQL function for doing query benchmarking – BENCHMARK(LOOP, STATEMENT). For example, BENCHMARK(1000, 1+1) will run 1+1 1000 times, and return the average execution time.

But please take extra note, as indicated in the MYSQL manual itself – The BENCHMARK() function is meant for measuring scalar expressions. Meaning, the statement must return a single value. Testing SELECT queries such as BENCHMARK(1000, SELECT * FROM TABLE) will not work, since it returns multiple rows and columns. A big bummer, but there are other ways to benchmark SELECT queries.

 

 

5) MANUAL BENCHMARK SCRIPT

5-test.php
<?php
// (A) CONNECT TO DATABASE
$dbhost = "localhost";
$dbname = "test";
$dbchar = "utf8";
$dbuser = "root";
$dbpass = "";
$dbset = "mysql:host=$dbhost;dbname=$dbname;charset=$dbchar";
$pdo = new PDO($dbset, $dbuser, $dbpass);

// (B) TEST SETTINGS
$sql = "SELECT * FROM `users`";
$params = null;
$runs = 30000;

// (C) TIME QUERY
$start = microtime(true);
$stmt = $pdo->prepare($sql);
for ($i=0; $i<$runs; $i++) { $stmt->execute($params); }
$end = microtime(true);

// (D) CLOSE & RESULTS
$taken = $end - $start;
$average = $taken / $runs;
echo "TOTAL - $taken seconds | AVERAGE - $average seconds";

Following up with the above “average query speed”, we can write a simple test script to loop through the query multiple times. Then calculate the total time taken and the average per run.

P.S. This one is in PHP, but it can really be any other language that can connect to MYSQL… You can even write a stored function or procedure in “pure MYSQL” if you want.

 

6) USE A BENCHMARKING TOOL

Lastly, before the toxic trolls start to rage about “proper benchmarking” – Yes, there are a ton of database benchmarking tools. I am not going to list every single one here, but here is what the official MYSQL community recommends:

  • DBT2
  • SysBench
  • flexAsynch – Used to test MYSQL Cluster. Already included in the source.

 

 

USEFUL BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEAT SHEET

Benchmark MYSQL Query (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *