MySQL Forums
Forum List  »  MyISAM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
second execution of the query is faster even the query cache is disabled
7079
November 08, 2013 01:21AM


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.