Measuring actual MySQL query time

mysql optimization
Mikhail picture Mikhail · Jun 30, 2012 · Viewed 97.2k times · Source

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

fancyPants picture fancyPants · Jun 30, 2012

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.

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Storing JSON in database vs. having a new column for each key

I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format. uid | meta -------------------------------------------------- 1 | {name:[…

Read More