Query run slower after no access for couple of hours or days, CHECK TABLE will help, but why?

[aman]

Hello,

I have a query join a couple of tables, the biggest one of which has 12 million rows and an index that will be used in the query. The query uses group by and order by and speed is optimized to 150ms normally. However, If no query sent in couples of hours or days, the query may slow down to 30 seconds. If I run CHECK TABLE to the biggest table, then the query will speed up to 150ms again. Does anybody know what happened?

Thank you very much,

Luise

[scoundrel]

No queries for a couple of days is almost always = all data has been washed out of OS and mysql caches. Check table statement reads an entire table and fills OS buffers again. So consequitive queries would be fast.

[aman]

Thank you so much for your reply. It’s same as what I guess. That helps me a lot definitely.

[aman]

Sorry, I still have a question, the first query will take about 30sec, and the subsequent queries will take about 10sec. Actually, this server is a development server, so nobody will touch it except me. It only can be accelerated to 150ms level until I run CHECK TABLE. Is there something else behind the scene?

Thank you so much

[scoundrel]

Post your query (SELECT…) here along with “ANALYZE SELECT…” query results and we’ll try to help. Even 150ms is way too much for a query on development machine (machine with a small dataset).