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 have use mysql profiler to optimize an opencart query which takes more than 10 seconds to executes.

mysql> SHOW PROFILES;
+----------+-------------+---------------------------------------------------  
| Query_ID | Duration    | Query                                                            
+----------+-------------+---------------------------------------------------    ---------------
|        1 |  0.00009000 | SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review ....
|        2 |  0.00012000 | SELECT DATABASE()                                                
|        3 |  0.00044900 | show databases                                                       
|        4 |  0.00186700 | show tables                                                      
|        5 | 12.91228300 | SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review ....
+----------+-------------+------------------------------------------------------------------

As shown, query 5 takes 12.9 sec to execute.

To get details i have used SHOW PROFILE

mysql> SHOW PROFILE FOR QUERY 5;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000003 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000051 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000003 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000048 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000002 |
| Sorting result     | 0.000032 |
| Sending data       | 0.000056 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000003 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000049 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000050 |
| executing          | 0.000004 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000044 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000003 |
| Sorting result     | 0.000029 |
| Sending data       | 0.000044 |
| executing          | 0.000005 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000029 |
| Sending data       | 0.000042 |
| executing          | 0.000005 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000029 |
| Sending data       | 0.000045 |
| executing          | 0.000005 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000029 |
| Sending data       | 0.000043 |
| executing          | 0.000011 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000045 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000043 |
| executing          | 0.000005 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000029 |
| Sending data       | 0.000056 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000002 |
| Sorting result     | 0.000031 |
| Sending data       | 0.000058 |
| executing          | 0.000004 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000052 |
| executing          | 0.000005 |
| executing          | 0.000002 |
| executing          | 0.000002 |
| Sorting result     | 0.000031 |
| Sending data       | 0.000046 |
| executing          | 0.000004 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.000053 |
| executing          | 0.000004 |
| executing          | 0.000003 |
| executing          | 0.000002 |
| Sorting result     | 0.000030 |
| Sending data       | 0.056232 |
| Sorting result     | 0.745130 |
| Sending data       | 0.000064 |
| end                | 0.000006 |
| removing tmp table | 0.184907 |
| end                | 0.000023 |
| query end          | 0.000004 |
| freeing items      | 0.000278 |
| logging slow query | 0.000006 |
| logging slow query | 0.002091 |
| cleaning up        | 0.000012 |
+--------------------+----------+
100 rows in set (0.00 sec)

That does't even add up to 1 second. Why is such a diffrence?

share|improve this question
    
What version are you using? Did you look for any bugs on bugs.mysql.com? –  eroomydna May 24 at 9:47
    
@eroomydna mysql version is 5.1.68-cll, Also thanks for the heads-up, found a similar bug bugs.mysql.com/bug.php?id=52487 –  user1497765 May 26 at 3:46

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.