After writing SQL statements in MySQL, how to measure the speed / performance of them?

Question

I saw something from an "execution plan" article:

10 rows fetched in 0.0003s (0.7344s)

(the link: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ )

How come there are 2 durations shown? What if I don't have large data set yet. For example, if I have only 20, 50, or even just 100 records, I can't really measure how faster 2 different SQL statements compare in term of speed in real life situation? In other words, there needs to be at least hundreds of thousands of records, or even a million records to accurately compares the performance of those 2 different SQL statements?

Answer 1

Generally when there are 2 times shown, one is CPU time and one is wall-clock time. I cannot recall which is which, but it appears that the first is the CPU time and the second is elapsed time.

Answer 2

The best way to test and compare performance of operations is often (if not always !) to work with a realistic set of data.

If you plan on having millions of rows when your application is in production, then, you should test with millions of rows right now, and not only a dozen !

A couple of tips :

• While benchmarking, use select SQL_NO_CACHE ..., instead of select ...
  • This will prevent MySQL from using its query cache (which would make the first query take a normal amount of time, and re-executing it several times a lot faster)
• Learn how to use EXPLAIN, and understand its output
• Read the Chapter 7. Optimization section of the manual ;-)

Answer 3

For your first question:

X row(s) fetched in Y s (Z s)

X = number of rows (of course); Y = time it took the MySQL server to execute the query (parse, retrieve, send); Z = time the resultset spent in transit from the server to the client;

(Source: http://forums.mysql.com/read.php?108,51989,210628#msg-210628)

For the second question, you will never ever know how the query performs unless you test with a realistic number of records. Here is a good example of how to benchmark correctly: http://www.mysqlperformanceblog.com/2010/04/21/mysql-5-5-4-in-tpcc-like-workload/

That blog in general as well as the book "High Performance MySQL" is a goldmine.