I believe this is a site admin question rather than a mysql one... My BlueHost-hosted sites are giving a "This Account Has Been Suspended" error. I phoned them up last night and they said that they were getting a performance error from mysql on my main account (under 3000PV per day), so they re-enabled my CPanel, I checked my phpMyAdmin and found one unindexed table for the IP To Country plugin has grown to half a million entries - there's no code to purge their cache table! I flushed the table, BH turned everything back on, but my account got suspended again about 8 hours later. Bog-standard WordPress 2.5 code with just a few other plugins like Subscribe To Comments seems to be enough to produce errors. Anyone got any suggestions as to how to debug such a site? Understand the log messages? One thing I noticed looking in other error logs was that there was a series of "Zend Optimiser has failed" errors. Oh, and I've not added anything new to the blog in the last week. I have about 1,300 posts and 4,000 active comments, so it's not a huge DB by any measure.
a long shot, but maybe you are exceeding the disk space usage? When you delete from a db, you don't actually regain any of the space (in most versions of mysql) This wouldn't be the case if you are using 5.1 (and maybe 5.0, i don't remember exactly when it was implemented)
Actually, BlueHost has unlimited disk space, and the problem specifically mentioned was mysql taking too long somewhere in my blog db. Another culprit might be Subscribe to Comments, but that is such a mainstream plugin that surely that can't be the problem? I'll phone up again tonight, turn off both plugins and see how it goes. Searching the web turns up a just a few other people complaining about BH and mysql problems that point to the trouble being at BH's end, not mine.