Measuring actual MySQL query time

Question

How can I measure the execution time of a query without measuring the time it spends waiting for a lock release etc? My only idea was to continuously measure same query and record the fastest time.

Answer 1

Start the profiler with

SET profiling = 1; 

Then execute your Query.

With

SHOW PROFILES; 

you see a list of queries the profiler has statistics for. And finally you choose which query to examine with

SHOW PROFILE FOR QUERY 1; 

or whatever number your query has.

What you get is a list where exactly how much time was spent during the query.

More info in the manual.

Answer 2

The accepted Answer is becoming invalid...

SHOW PROFILE[S] are deprecated as of MySQL 5.6.7 and will be removed in a future MySQL release. Use the Performance Schema instead; see http://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profiling.html