Calculating Time of a query php & Mysql

Question

I am trying to calculate the time it takes a query to run using a php script:

For example:

$sql = "SELECT COUNT(*) FROM `order_items` LEFT JOIN `orders` ON `oi_o_id` = `o_id` WHERE `o_status` = 'completed' AND `oi_p_id` = '10' LIMIT 1";
$sqlStart = getMicroTime();
$result = mysql_query($sql);
$sqlEnd = getMicroTime();
$sqlTime = $sqlEnd - $sqlStart;

echo $sqlTime;

function getMicroTime() {
    list($msec, $sec) = explode(' ', microtime());
    return floor($sec / 1000) + $msec;
}

This appears to work most of the time, but occasionally I get a negative value e.g. -0.98840499995276. How is this possible? and is there a better way to get the query execution time?

Thanks

Don't seem to ever get a negative number: codepad.org/rL6MWCoK — Naftali
– Naftali, Commented Nov 16, 2011 at 17:41
Unfortunately I do, thanks for Marc B for actually answering the question — Lizard
– Lizard, Commented Nov 16, 2011 at 17:46
@Neal Actually it does fail - I just increased to 1000 iterations and it failed on around 986 codepad.org/OVf9aoOR — Lizard
– Lizard, Commented Nov 16, 2011 at 18:05
It'd appear to be a timing glitch of some sort. Some minor mods to @Lizard's version and there's no negative times: codepad.org/UetGzU3l — Marc B
– Marc B, Commented Nov 16, 2011 at 19:55

Marc B · Accepted Answer · 2011-11-16 17:40:58Z

8

Your microtime function is not necessary, simply do

$start = microtime(TRUE);
... do query
$end = microtime(TRUE);

passing in the TRUE value has microtime() return the timestamp as an actual float, not that moronic string format that some [truly nasty but accurate description of said person's intelligence] thought would be a useful way of returning the data.

answered Nov 16, 2011 at 17:40

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

5 Comments

Adrian Cornish Over a year ago

With microtime() false this - $time = $time_end - $time_start; echo "Did nothing in $time_end - $time_start=$time seconds</br>"; produces this output - but somehow PHP got the math right - any idea what its actually is doing here? Did nothing in 0.55040500 1321470419 - 0.55024300 1321470419=0.000162 seconds

Marc B Over a year ago

with false, it returns a string. substracting two strings is essentially meaningless.

Adrian Cornish Over a year ago

I agree - but when I tried it - it appears to work. Thats the strange thing.

Marc B Over a year ago

because PHP will use the first number-looking bit it finds, which happens to be the msec value. If the start is '987 456' and the end is '123 457' (real diff: 0.136 sec), you'll end up doing 123-987 = -864.

Adrian Cornish Over a year ago

I understand the OP's problem - I am wonder why its working in php 5.3.6. Try this $time_start = microtime(false); usleep(100); $time_end = microtime(false); $time = $time_end - $time_start; echo "Did nothing in $time_end - $time_start=$time seconds</br>"; It is producing the correct result somehow

Miguel Bonera · Accepted Answer · 2013-03-16 18:32:14Z

5

Sometimes PHP use the 'E notation' to represent floating point numbers: if you wish to avoid this, you can use the round function in this way:

$start_time = (float) round(microtime(true) * 10000,0);
{your code here}
$finish_time = (float) round(microtime(true) * 10000,0);
$time = ($finish_time - $start_time) / 10000;

answered Mar 16, 2013 at 18:32

Miguel Bonera

511 silver badge3 bronze badges

Comments

Adrian Cornish · Accepted Answer · 2011-11-16 19:07:47Z

1

Hmm works for me - PHP manual does say this. Have you checked for subspace time anomalies :-)

This function is only available on operating systems that support the gettimeofday() system call.

Also tried the alternative method from PHP manual

$time_start = microtime(true);

// Sleep for a while
usleep(100);

$time_end = microtime(true);
$time = $time_end - $time_start;

echo "Did nothing in $time seconds\n";

edited Nov 16, 2011 at 19:07

answered Nov 16, 2011 at 17:45

Adrian Cornish

24k14 gold badges64 silver badges81 bronze badges

Collectives™ on Stack Overflow

Calculating Time of a query php & Mysql

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related