Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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.

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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