up vote 2 down vote favorite
share [g+] share [fb]

I have a simple table with 3M records and a simple unique index on an integer field. When selecing

SELECT * FROM ThisTable ORDER BY ThatField LIMIT 10

It works very fast, as expected. When I insert or delete a record though, or restart MySQL, this same query takes like 10-20 seconds, sometimes longer.

I am guessing that it's loading index into memory - that's great, but why client should wait? Can this be fixed so that the query returns fast always?

Thank you very much for your help, Andrew

link|improve this question
feedback

1 Answer

up vote 4 down vote

The easiest way you'll be able to get this query into cache as soon as the engine restarts is to run the select as soon as the engine restarts.

A script file that restarts the MySQL process, and then immediately logs into the MySQL server and executes SELECT * FROM ThisTable ORDER BY ThatField LIMIT 10 will ensure that the query is cached immediately, meaning that the next query should be nicely cached.

link|improve this answer
@KOHb This process is commonly called "warming the cache' or "warm up cache". It's a common process for sites that depend on the caching layer. – Rob Olmos Nov 8 '10 at 21:16
Sorry, I don't understand... When do I need to run this cache "warm up" query exactly? On every update/delete done to ThisTable? But this could be an overkill, no? – KOHb Nov 13 '10 at 16:38
And this script involves restarting of MySQL process, I think it's definitely not something you want to do on every update. Could you please clarify? I have multiple users working with the web site, and I don't really want to restart MySQL every time someone makes a change in that table. – KOHb Nov 13 '10 at 16:41
@KOHb - that's only good for when you restart MySQL. Sorry I missed the part about "insert or delete". – Mark Henderson♦ Nov 13 '10 at 20:27
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.