[Resolved] SQL Query time?

Nanoblaze

I looked around a bit, and couldnt find anything specific on calculating the amount of time a query takes, so here i am.

So My question:

Is there a way to tell how long a query took/is taking?

Thanks in advance.

nemonoman

Well there's the stupid obvious:

SELECT NOW();

SELECT * FROM yourtables

SELECT NOW();

For most queries, there's likely to be only a fraction of second required.

If you've got a LONG query going in mySQL, try:

SHOW PROCESSLIST

This will tell how long the query has been running, and what stage the db has got to.

My impression is that if you have enough time to invoke a "SHOW PROCESSLIST", you've really screwed up your query.

The main reason for using a SHOW PROCESSLIST is to find the id of the ugly query and follow it with a

KILL id command.

In point of fact if your queries are taking so long you're wondering how long they're taking, it means you need to optimize the query.

Start by using EXPLAIN

EXPLAIN SELECT somecolumn FROM sometable, someothertable, somethird table WHERE ....

Will cause mySQL to describe the way it's finding its answer.

You can then start indexing, etc.

The MySQL documentation on query optimization is very helpful.

www.mySQL.com

Nanoblaze

Well, it wasnt really that i was having a problem, i just wanted to know how to do it ( similar to how vbulliten can be modified to do it ) so i'd know if i was having any speed issues at any time. Thanks for the help, I'll see if i can work something out from there.

Nanoblaze

Ok... While using SELECT NOW() its displayed as resource id #4 ( when echo'd by php ) so what information would need to be pulled from it?

Nanoblaze

nevermind, thats not quite what i need...im looking for actual query time ( which in most cases should be under 1 second )

Page generated in 0.16836202 seconds (72.98% PHP - 27.02% MySQL) with 19 queries.

similar to that, how vbull does it... unless theres a way to modify select now() to give the date in a raw format that wouldnt really work.

nemonoman

Sorry, I didn't realize the extent of your current knowledge:

$startresult=mysql_query("SELECT NOW()");

$startrow=mysql_fetch_array($startresult));
$start=$startrow[0];

//do your other stuff here;

$endresult=mysql_query("SELECT NOW()");

$endrow=mysql_fetch_array($endresult));
$end=$endrow[0];

$timespent=$end-$start;

echo "This all took $timespent";

Nanoblaze

Ok, that seems to be partially working, though the precision is a bit lacking... Do you know of any way to do something like a C style setprecision? ( read the the <x>th place or whatnot )

nemonoman

You might want to look at the mysql documentation

mysql.com

See "DATE and TIME functions"

OR consider the PHP microtime() function

http://www.phpbuilder.com/manual/function.microtime.php

Of course that function would entail turning strings into an integer, and also checking the values returned with a "time()" function, and it might, I think take some effort on your part. But anything worth doing is worth doing well.

Nanoblaze

The microtime function was EXACTLY what i was looking for, using a slightly modified example to set precision... here is what i have, for future requests...

function getmicrotime() { 
   list($usec, $sec) = explode(" ",microtime()); 
   return ((float)$usec + (float)$sec); 
} 

$time_start = getmicrotime();

//rest of your code

$time_end = getmicrotime();
$time = $time_end - $time_start;
$time = round($time,4);
echo "Did whatever in $time seconds";