How do I test the real execution time of a query?

Question

I get one slow query in Mysql slow query log. Mysql slow query log shows the query need more than 4 seconds to execute.

I run this query in phpmyadmin, it takes 3 seconds. When I run it again, it takes only 0.0002 seconds. I guess there is a DB Cache or something. When I run it in second time, it executing time might not be accurate.

For this kind of situation(executing time is quick), how do I test the real execution time of the query?

possible duplicate of [Measuring actual MySQL query time](http://stackoverflow.com/questions/11274892/measuring-actual-mysql-query-time) — Boaz, Nov 14 '13 at 23:08

score 2 · Accepted Answer · answered Nov 14 '13 at 23:08

2

For testing purpose only you can use SQL_NO_CACHE

SELECT SQL_NO_CACHE * FROM `table` .....

other way you can set query_cache_type to 0 for current session

SET SESSION query_cache_type=0;

SQL_NO_CACHE

Query Cache Configuration

answered Nov 14 '13 at 23:08

M Khalid Junaid

63,861
10
90
118

score 1 · Answer 2 · answered Nov 14 '13 at 23:08

1

If you are talking about preventing MySQL from caching the result, you can use the SQL_NO_CACHE keyword. (eg: SELECT SQL_NO_CACHE * FROM table1)

answered Nov 14 '13 at 23:08

Andy Senn

649
5
13

score 0 · Answer 3 · answered Jan 08 '16 at 13:47

You can modify your query a little before executing it second time.

So following two queries are regarded as different by the query cache:

SELECT * FROM tbl_name
Select * from tbl_name

A query cannot be cached if it contains some special functions like CONNECTION_ID(). You can add this function to your query:

SELECT *,CONNECTION_ID() FROM tbl_name

How the Query Cache Operates

How do I test the real execution time of a query?

3 Answers3