Large MySQL Table - Advice Needed

I have a large mysql MyISAM table with 1.5mil rows and 4.5GB big, still increasing everyday.

I have done all the necessary indexing and the performance has been greatly optimized. Yet, the database occasionally break down (showing 500 Internal Server error) usually due to query overload. Whenever there is a break down, the table will start to work very slowly and I'll have to do a silly but effective task : copy the entire table over to a new table and replace the new one with the old one!!

You may ask why such a stupid action. Why not repair or optimize the table? I've 开发者_运维问答tried that but the time to do repair or optimization may be more than the time to simply duplicate the table and more importantly the new table performs much faster.

Newly built table usually work very well. But over time, it will become sluggish (maybe after a month) and eventually lead to another break down (500 internal server). That's when everything slow down significantly and I need to repeat the silly process of replacing table.

For your info: - The data in the table seldom get deleted. So there isn't a lot of overhead in the table. - Under optimal condition, each query takes 1-3 secs. But when it becomes sluggish, the same query can take more than 30 seconds. - The table has 24 fields, 7 are int, 3 are text, 5 are varchar and the rest are smallint. It's used to hold articles.

If you can explain what cause the sluggishness or you have suggestion on how to improve the situation, feel free to share it. I will be very thankful.

Consider moving to InnoDB. One of its advantages is that it's crash safe. If you need full text capabilities, you can achieve that by implementing external tools like Sphinx or Lucene.

Partitioning is a common strategy here. You might be able to partition the articles by what month they were committed to the database (for example) and then have your query account for returning results from the month of interest (how you partition the table would be up to you and your application's design/behavior). You can union results if you will need your results to come from more than one table.

Even better, depending on your MySQL version, partitioning may be supported by your server. See this for details.