Geek Answers Handbook

How to check MySQL query speed with less inconsistencies?

I need an easy way to test SQL queries for speed. I'm not worried about hardware differences; I basically need a relative number.

This is what I did with PHP (its fuzzy, but works):

// CONNECT TO DB HERE $sub = new YomoSubscription(95,783); $t = microtime(TRUE); // contains the SQL db call i'm testing $fp = $sub->generateFingerprint(); echo microtime(TRUE)-$t;

PROBLEM I have that sometimes when I first connect / start my test takes, for example, 1.25 s. However, on subsequent connections, it takes 0.004 seconds ... Why is this ?

I am sure MySQL query cache is disabled in my.ini:

 query_cache_size=0
+6
performance php mysql
source share
4 answers

Your first query may be slower because MySQL actually pushes the disk on the first query, not the second.

Your operating system may cache files in memory as they are read; as a result, subsequent readings may not be necessary to actually get to disk and will return much faster.

Typically, I usually run a query several times and look for consistency. Most often, the first run will take several times longer, and the 2nd and 4th takes about the same time. These subsequent runs are probably more characteristic of the performance that you will see in a real production system, since your production database should store this data in the OS cache, while your development system rarely gets access.

After all, when it comes to query performance, for the most part you should just give it a quick walk in development and monitor the slow query log during production to see which queries are really needed.

For software-based queries for performance data, take a few samples and use the median. But in practice, this will not be terribly representative of the actual performance problems that you will encounter during the production process.

+4
source share

Try using SELECT BENCHMARK (time, query)

Additional information: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark

+8
source share

Let's pretend that:

  • You are not using persistent connection.
  • database installed on the server where statistics are running (no network connection)
  • nobody uses a database (row / table locks)
  • another difficult process is not running
  • etc....

If you really want to test your request, you should do the following:

 $database->query('SET SESSION query_cache_type = OFF');

Then you run the request 2-3 times in a loop (to "warm up" the server).

And only then:

 $database->query('FLUSH STATUS'); #If you use the stats to profile your query $t = microtime(TRUE); $fp = $sub->generateFingerprint(); echo microtime(TRUE)-$t; $database->query('SHOW STATUS');

Et voila !! :)))

BTW, request speed is one of the parameters to read. Learn how to read the very valuable information returned by SHOW STATUS and EXPLAIN ... It will be much better.

Here is a link that you will love: http://www.xaprb.com/blog/2006/10/12/how-to-profile-a-query-in-mysql/

Enjoy. :)

+2
source share

You might be using persistent connections in your class. Pconnect will reuse the connection and will consider this type of delay.

0
source share

More articles:


All Articles