CODEWITHSUNDEEP
mysqlperformancemysql-slow-query-log
Tester
Tester

Reputation: 808

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

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?

Upvotes: 0

Views: 665

Answers (3)

user1209304
user1209304

Reputation: 428

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

Upvotes: 0

Andy Senn
Andy Senn

Reputation: 649

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)

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

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

Upvotes: 2

Related Questions