mysql: same select slow even if table does not change
up vote
1
down vote
favorite
Table ads has about 0.5 mio rows.
Time to time I use
select sql_calc_found_rows * from `ads`
where cat1<>24 and
curdate()<=expiry and
offl=0
order by ads.date desc ,id desc
limit 0,30
Every time it needs almost the same time - even if ads does not change. does mysql not notice this fact?? and speed up??? so when query comes again, time should almost be zero.
mysql performance
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
asked Nov 22 at 8:32
Peter
41
add a comment |
up vote
1
down vote
favorite
Table ads has about 0.5 mio rows.
Time to time I use
select sql_calc_found_rows * from `ads`
where cat1<>24 and
curdate()<=expiry and
offl=0
order by ads.date desc ,id desc
limit 0,30
Every time it needs almost the same time - even if ads does not change. does mysql not notice this fact?? and speed up??? so when query comes again, time should almost be zero.
mysql performance
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
asked Nov 22 at 8:32
Peter
41
1
You can enable query_cache. Refer: dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
– Madhur Bhaiya
Nov 22 at 8:49
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
Table ads has about 0.5 mio rows.
Time to time I use
select sql_calc_found_rows * from `ads`
where cat1<>24 and
curdate()<=expiry and
offl=0
order by ads.date desc ,id desc
limit 0,30
Every time it needs almost the same time - even if ads does not change. does mysql not notice this fact?? and speed up??? so when query comes again, time should almost be zero.
mysql performance
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
asked Nov 22 at 8:32
Peter
41
Table ads has about 0.5 mio rows.
Time to time I use
select sql_calc_found_rows * from `ads`
where cat1<>24 and
curdate()<=expiry and
offl=0
order by ads.date desc ,id desc
limit 0,30
Every time it needs almost the same time - even if ads does not change. does mysql not notice this fact?? and speed up??? so when query comes again, time should almost be zero.
mysql performance
mysql performance
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
asked Nov 22 at 8:32
Peter
41
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
asked Nov 22 at 8:32
Peter
41
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
edited Nov 22 at 8:47
Madhur Bhaiya
19.1k62236
19.1k62236
asked Nov 22 at 8:32
Peter
41
asked Nov 22 at 8:32
Peter
41
asked Nov 22 at 8:32
Peter
41
41
1
You can enable query_cache. Refer: dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
– Madhur Bhaiya
Nov 22 at 8:49
add a comment |
1
You can enable query_cache. Refer: dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
– Madhur Bhaiya
Nov 22 at 8:49
1
1
You can enable query_cache. Refer: dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
– Madhur Bhaiya
Nov 22 at 8:49
You can enable query_cache. Refer: dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
– Madhur Bhaiya
Nov 22 at 8:49
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
Query Cache
- If the QC is configured, and
- If the table has not changed since the identical query was run, and
- If a few other subtle things are satisfied, then
The QC will very quickly deliver the saved resultset. However, because of the restriction on the table not changed, Production systems rarely benefit from the QC.
innodb_buffer_pool
The first time a query is run, the data is probably sitting on disk. So the query must fetch the data and put it into the buffer_pool to act on it.
The subsequent times the query is run, the data may still be in that cache. If so, it will be faster.
Indexes
Proper indexing is your first line of defense for speeding up queries. That particular query may benefit from one of the following. (It is hard to tell which will be best; only one will be used.)
INDEX(date, id)
INDEX(offl, expiry)
INDEX(offl, cat1, expiry)
answered Nov 23 at 2:51
Rick James
65.2k55796
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Query Cache
- If the QC is configured, and
- If the table has not changed since the identical query was run, and
- If a few other subtle things are satisfied, then
The QC will very quickly deliver the saved resultset. However, because of the restriction on the table not changed, Production systems rarely benefit from the QC.
innodb_buffer_pool
The first time a query is run, the data is probably sitting on disk. So the query must fetch the data and put it into the buffer_pool to act on it.
The subsequent times the query is run, the data may still be in that cache. If so, it will be faster.
Indexes
Proper indexing is your first line of defense for speeding up queries. That particular query may benefit from one of the following. (It is hard to tell which will be best; only one will be used.)
INDEX(date, id)
INDEX(offl, expiry)
INDEX(offl, cat1, expiry)
answered Nov 23 at 2:51
Rick James
65.2k55796
add a comment |
up vote
0
down vote
Query Cache
- If the QC is configured, and
- If the table has not changed since the identical query was run, and
- If a few other subtle things are satisfied, then
The QC will very quickly deliver the saved resultset. However, because of the restriction on the table not changed, Production systems rarely benefit from the QC.
innodb_buffer_pool
The first time a query is run, the data is probably sitting on disk. So the query must fetch the data and put it into the buffer_pool to act on it.
The subsequent times the query is run, the data may still be in that cache. If so, it will be faster.
Indexes
Proper indexing is your first line of defense for speeding up queries. That particular query may benefit from one of the following. (It is hard to tell which will be best; only one will be used.)
INDEX(date, id)
INDEX(offl, expiry)
INDEX(offl, cat1, expiry)
answered Nov 23 at 2:51
Rick James
65.2k55796
add a comment |
up vote
0
down vote
up vote
0
down vote
Query Cache
- If the QC is configured, and
- If the table has not changed since the identical query was run, and
- If a few other subtle things are satisfied, then
The QC will very quickly deliver the saved resultset. However, because of the restriction on the table not changed, Production systems rarely benefit from the QC.
innodb_buffer_pool
The first time a query is run, the data is probably sitting on disk. So the query must fetch the data and put it into the buffer_pool to act on it.
The subsequent times the query is run, the data may still be in that cache. If so, it will be faster.
Indexes
Proper indexing is your first line of defense for speeding up queries. That particular query may benefit from one of the following. (It is hard to tell which will be best; only one will be used.)
INDEX(date, id)
INDEX(offl, expiry)
INDEX(offl, cat1, expiry)
answered Nov 23 at 2:51
Rick James
65.2k55796
Query Cache
- If the QC is configured, and
- If the table has not changed since the identical query was run, and
- If a few other subtle things are satisfied, then
The QC will very quickly deliver the saved resultset. However, because of the restriction on the table not changed, Production systems rarely benefit from the QC.
innodb_buffer_pool
The first time a query is run, the data is probably sitting on disk. So the query must fetch the data and put it into the buffer_pool to act on it.
The subsequent times the query is run, the data may still be in that cache. If so, it will be faster.
Indexes
Proper indexing is your first line of defense for speeding up queries. That particular query may benefit from one of the following. (It is hard to tell which will be best; only one will be used.)
INDEX(date, id)
INDEX(offl, expiry)
INDEX(offl, cat1, expiry)
answered Nov 23 at 2:51
Rick James
65.2k55796
answered Nov 23 at 2:51
Rick James
65.2k55796
answered Nov 23 at 2:51
Rick James
65.2k55796
answered Nov 23 at 2:51
Rick James
65.2k55796
65.2k55796
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53426733%2fmysql-same-select-slow-even-if-table-does-not-change%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You can enable query_cache. Refer: dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html
– Madhur Bhaiya
Nov 22 at 8:49