Understanding query optimisation

Databases
1

Hi,

I have a query which according to phpMyAdmin, takes 0.2 seconds to complete. seems quite long. However, when I profile it, the output is as below and it doesn’t add up to anything like 0.2 secs.

Presumably I am not aware of something that makes the difference. what am I missing please?

Status Time
starting 0.000045
Opening tables 0.000040
System lock 0.000004
Table lock 0.000010
init 0.000014
optimizing 0.000006
statistics 0.000025
preparing 0.000012
executing 0.000040
Sending data 0.000182
end 0.000006
end 0.000004
query end 0.000005
freeing items 0.000006
closing tables 0.000004
removing tmp table 0.000010
closing tables 0.000007
logging slow query 0.000004
cleaning up 0.000004

In my head that adds up to 0.000419 secs.

2

Did you flush all caches between the first run and the profiling? You might want to restart the MySQL server wholesale as well. There’s query caches, table caches, and the operating system level file caches that can be involved in making subsequent queries faster than the first time they’re run.

3

Thanks Dan,

I’ll look into that

bazz