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