find time to execute MySQL query via PHP

Question

I've seen this question around the internet (here and here, for example), but I've never seen a good answer. Is it possible to find the length of time a given MySQL query (executed via mysql_query) took via PHP?

Some places recommend using php's microtime function, but this seems like it may be inaccurate. The mysql_query may be bogged down by network latency, or a sluggish system which isn't responding to your query quickly, or some other unrelated cause. None of these are directly related to the quality of your query, which is the only thing I really want to test out here. (Please mention in the comments if you disagree!)

Answer 1

I you are only checking the quality of the query itself, then remove PHP from the equation. Use a tool like the MySQL Query Browser or SQLyog.

Or if you have shell access, just connect directly. Any of these methods will be superior in determining the actual performance of your queries.

Answer 2

At the php level you pretty much would need to record the time before and after the query.

If you only care about the query performance itself you can enable the slow query log in your mysql server: http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html That will log all queries longer than a specified number of seconds.

Answer 3

If you really need query information maybe you could make use of SHOW PROFILES:

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

Personally, I would use a combination of microtime-ing, the slow query log, mytop, and analyzing problem queries with the MySQL client (command line).

Answer 4

My answer is similar, but varied. Record the time before and after the query, but do it within your database query class. Oh, you say you are using mysql_query directly? Well, now you know why you should use a class wrapper around those raw php database functions (pardon the snark). Actually, one is already built called PDO:

http://us2.php.net/pdo

If you want to extend the functionality to do timing around each of your queries... extend the class! Simple enough, right?