3

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!)

Improve this question
2
  • If network latency or a sluggish system are preventing your queries from executing in a timely manner, that's probably the issue you should be addressing. When it comes to your production application, the total round trip time is what will determine the render time of your page. Commented Sep 25, 2009 at 0:13
  • 3
    Actually, I disagree. As an engineer, I want to optimize every step of the process, and that requires knowing where the lag is in each step of the process. Sure, the end result is the same, but its much more helpful to me if I can see exactly where the lag is; network latency, query time, unreasonable server load, etc. As stated in the comment below, with 20+ queries on a given page, its difficult to optimize them individually... I'd rather see them all at once, hence the question. Commented Sep 25, 2009 at 14:52

4 Answers 4

Reset to default
1

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:

https://www.php.net/pdo

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

The thing with this and the other answers below is that, as a developer, its not always practical to do these in the shell. In a given script I may have 20 separate queries executing, with the output of one feeding another. And even if I do execute all 20 queries in the shell, that would be equivalent to loading the page one time, with one set of variables. If I change something I'll have to do the whole exercise again. It would be much easier to have a way to - within PHP - execute a query, capture the results (timing and all), and display it there on the page.
This does not answer the question. I found this question because I want a way to add query time into PHP script execution time. Even if a MySQL query takes 30 seconds, PHP will output 0.004s to your screen because it does not count the time it waited for MySQL. That makes sense, but I want to get both.
0

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.

Improve this answer

Comments

0

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).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.