The importance of mysql optimization
Sometimes you forget the small things... For example, recently at work we switched our website over to Drupal (which powers this site too) and after a few days I noticed it hanging on occasion, for 1-2 minutes. I check the mysql slow query log I have set up, and found that a certain query was taking 1-2 minutes to execute. YOWSA! I dug in and found that the query was called to delete the cache of the menu structure every time the menu *may* have changed, which happens when you update some settings or edit/create a new page. And I checked the database file on the disk and it measured in at a whopping 2.1 Gigs.
Now my first thought was, why the heck is drupal doing such a stupid query? It was stupid, not an optimized query at all, but the only way to get the query done the way they had structured the table. Grrr. But then for the fun of it, I tried optimizing the mysql table, which is kind of like defragging a hard disk. It gets rid of all the crunk laying around that isn't needed anymore but hasn't been removed from disk yet.
Amazingly, this one simple command shrunk the cache file from 2.1 Gigs to 20 MB, and the query time went from 1-2 minutes to 0.6 seconds.
I've learned my lesson and now all the tables are being optimized every night.