Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

yes, you can use profiling, for an example query SELECT * FROM Country LIMIT 5; you will need to do the follow:

mysql> SET profiling = 1;
mysql> SELECT * FROM Country LIMIT 5;
. . .
mysql> SHOW PROFILES;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.02295675 | SELECT DATABASE()             |
|        2 | 0.00056225 | show databases                |
|        3 | 0.00017600 | show tables                   |
|        4 | 0.00016950 | select * FROM City            |
|        5 | 0.00027725 | show tables                   |
|        6 | 0.07000825 | select * FROM Country LIMIT 5 |
+----------+------------+-------------------------------+
mysql> SHOW PROFILE FOR QUERY 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000028 |
| Waiting for query cache lock   | 0.000005 |
| checking query cache for query | 0.000034 |
| checking permissions           | 0.000009 |
| Opening tables                 | 0.000735 |
| System lock                    | 0.033283 |
| Waiting for query cache lock   | 0.009419 |
| init                           | 0.000029 |
| optimizing                     | 0.000006 | <--- HERE IS THE OPTIMIZER TIME
| statistics                     | 0.000013 |
| preparing                      | 0.000010 |
| executing                      | 0.000005 |
| Sending data                   | 0.026332 |
| end                            | 0.000014 |
| query end                      | 0.000008 |
| closing tables                 | 0.000027 |
| freeing items                  | 0.000009 |
| Waiting for query cache lock   | 0.000004 |
| freeing items                  | 0.000017 |
| Waiting for query cache lock   | 0.000004 |
| freeing items                  | 0.000005 |
| storing result in query cache  | 0.000007 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+
24 rows in set (0.03 sec)

In my example query SELECT * FROM Country LIMIT 5; the optmizer took 0.000006

share|improve this answer
    
I love quick-and-dirty answers like this, so +1 !!! –  RolandoMySQLDBA May 28 '13 at 15:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.