Mysql – Testing Query Speed

MySQLPHPquerytestingwindows

When I'm running a query for the first time, it takes x miliseconds to be executed. When I run it the second time it takes a lot less time. I assume that it is some caching involved in this mechanism, but I need to know what is the real speed of my queries.

Can I reset the caching after the query instruction, or something like that?
In the developement stage, what are you using to see the speed of your queries?
Can you provide me a useful resource/tool/tutorial/blog to read about this thing/feature/problem?

BTW I'm using Apache, MySQL and PHP in Windows (XAMPP).

Best Answer

Personally, I tend to use the 2nd and subsequent executions as my benchmark when developing hence this answer...

Arguably the real speed is with caching of data and execution plan.

If it's run often enough to matter, then it will be cached in real life. You are measuring performance the 2nd time. For the first run, you're measuring load/compile times.

On a production server data will be cached too because of use, but on development it won't be.

It it's run once a day or as an occasional report then it doesn't matter (within reason).

Related Solutions

Mysql – Design for a Testing system

I've solved 2. using sub-querys

SELECT
  tr1.id AS id,
  dsv.idSite_id AS idSite,
  tr1.idSiteVersion_id AS idSiteVersion,
  tr1.idTest_id AS idTest,
  tr1.result AS result,
  tr1.timeStarted AS timeStarted,
FROM 
  dash.testresult AS tr1 
  NATURAL JOIN (
    SELECT idTest_id, idSiteVersion_id, MAX(timeEnd) AS timeEnd
    FROM dash.testresult
    WHERE timeEnd IS NOT NULL
    GROUP BY idTest_id, idSiteVersion_id
  ) AS tr2
  JOIN dash.siteversion AS dsv
    ON dsv.id = tr1.idSiteVersion_id
  JOIN dash.test AS dtest
    ON dtest.id = tr1.idTest_id AND dtest.ignored = 0

Mysql – What could be causing strange query timeouts between PHP and MySQL

For general profiling of what exactly is going on in the query plan, you can try PROFILING

It will basically help you determine where the hangup is.

Of course, it only works if you've compiled MySQL with enable-profiling.

Best Answer

Related Solutions

Mysql – Design for a Testing system

Mysql – What could be causing strange query timeouts between PHP and MySQL

Related Question