second execution of the query is faster even the query cache is disabled
Posted by:
David raj
Date: November 08, 2013 01:21AM
Hi,
Query caching is disabled in my server and when I ran the query second time the execution is much much faster than the first time.
But if I change the value what I gave in my query again the query is taking more time but only for the first execution.
MySQL version: 5.5.30
And all my tables are in MyISAM.
I am pasting the profiler output. Can anyone have any idea about this behaviour?
You can see the difference in "Copying to tmp table"
First time of execution:
+-------------------------+------------+-----------------------+---------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+-------------------------+------------+-----------------------+---------------+-------------+
| starting | 0.009494 | NULL | NULL | NULL |
| checking permissions | 0.000005 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000001 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000004 | check_access | sql_parse.cc | 4751 |
| Opening tables | 0.028032 | open_tables | sql_base.cc | 4831 |
| System lock | 0.000021 | mysql_lock_tables | lock.cc | 299 |
| init | 0.013522 | mysql_select | sql_select.cc | 2579 |
| optimizing | 0.000341 | optimize | sql_select.cc | 865 |
| statistics | 0.315518 | optimize | sql_select.cc | 1056 |
| preparing | 0.000049 | optimize | sql_select.cc | 1078 |
| FULLTEXT initialization | 0.000276 | init_ftfuncs | sql_base.cc | 9032 |
| Creating tmp table | 0.000037 | optimize | sql_select.cc | 1601 |
| executing | 0.000004 | exec | sql_select.cc | 1836 |
| Copying to tmp table | 138.033982 | exec | sql_select.cc | 1983 |
| Sorting result | 0.000073 | exec | sql_select.cc | 2267 |
| Sending data | 0.005455 | exec | sql_select.cc | 2380 |
| end | 0.001783 | mysql_select | sql_select.cc | 2615 |
| removing tmp table | 0.000008 | free_tmp_table | sql_select.cc | 11219 |
| end | 0.000003 | free_tmp_table | sql_select.cc | 11244 |
| query end | 0.000003 | mysql_execute_command | sql_parse.cc | 4440 |
| closing tables | 0.000009 | mysql_execute_command | sql_parse.cc | 4492 |
| freeing items | 0.000029 | mysql_parse | sql_parse.cc | 5640 |
| logging slow query | 0.000002 | log_slow_statement | sql_parse.cc | 1461 |
| logging slow query | 0.016585 | log_slow_statement | sql_parse.cc | 1470 |
| cleaning up | 0.000005 | dispatch_command | sql_parse.cc | 1417 |
+-------------------------+------------+-----------------------+---------------+-------------+
Second time of execution:
+-------------------------+----------+-----------------------+---------------+-------------+
| Status | Duration | Source_function | Source_file | Source_line |
+-------------------------+----------+-----------------------+---------------+-------------+
| starting | 0.000175 | NULL | NULL | NULL |
| checking permissions | 0.000004 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000001 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000002 | check_access | sql_parse.cc | 4751 |
| checking permissions | 0.000004 | check_access | sql_parse.cc | 4751 |
| Opening tables | 0.000030 | open_tables | sql_base.cc | 4831 |
| System lock | 0.000014 | mysql_lock_tables | lock.cc | 299 |
| init | 0.000081 | mysql_select | sql_select.cc | 2579 |
| optimizing | 0.000022 | optimize | sql_select.cc | 865 |
| statistics | 0.000859 | optimize | sql_select.cc | 1056 |
| preparing | 0.000042 | optimize | sql_select.cc | 1078 |
| FULLTEXT initialization | 0.000011 | init_ftfuncs | sql_base.cc | 9032 |
| Creating tmp table | 0.000023 | optimize | sql_select.cc | 1601 |
| executing | 0.000002 | exec | sql_select.cc | 1836 |
| Copying to tmp table | 4.428463 | exec | sql_select.cc | 1983 |
| Sorting result | 0.000068 | exec | sql_select.cc | 2267 |
| Sending data | 0.000024 | exec | sql_select.cc | 2380 |
| end | 0.000003 | mysql_select | sql_select.cc | 2615 |
| removing tmp table | 0.000008 | free_tmp_table | sql_select.cc | 11219 |
| end | 0.000003 | free_tmp_table | sql_select.cc | 11244 |
| query end | 0.000004 | mysql_execute_command | sql_parse.cc | 4440 |
| closing tables | 0.000013 | mysql_execute_command | sql_parse.cc | 4492 |
| freeing items | 0.000026 | mysql_parse | sql_parse.cc | 5640 |
| logging slow query | 0.000002 | log_slow_statement | sql_parse.cc | 1461 |
| cleaning up | 0.000003 | dispatch_command | sql_parse.cc | 1417 |
+-------------------------+----------+-----------------------+---------------+-------------+
Regards,
David
Subject
Views
Written By
Posted
second execution of the query is faster even the query cache is disabled
7098
November 08, 2013 01:21AM
3084
November 09, 2013 04:16PM
2459
November 11, 2013 03:02AM
2539
November 11, 2013 11:21PM
2038
November 13, 2013 12:32AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.