Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It seems all is good and when I suddenly get a lot of writes to the table the index seems to die and queries take up to 2 seconds. This always happens when I've got a lot of traffic so the knock on effects pretty much bring my database server down.

If I rebuild the index all is good again. What could be causing these sudden 'breaks' in the index? How can it be prevented?

I typically get about a thousand inserts into this over a 15 minute period which 'breaks' the index. If if remove and create the 'created_desc' index all is good again. It's just pretty scary to do such a think with a thousand inserts happening...
–
bradleyOct 15 '11 at 22:28

1 Answer
1

The problem is not really index fragmentation. It is more associated with the index statistics. If you are using MyISAM, sudden spikes of INSERTs would throw the statistics off in the eyes of the MySQL Query Optimizer. This would cause the MySQL Query Optimizer to take horrific guesses within the EXPLAIN plans of SELECT queries.