As far as site maintenance goes most WordPress users know the importance of keeping WordPress and plugins up to date. Some also know to keep frequent site backups. Few realize there is more to website maintenance than these two tasks. The health of your WordPress database is often overlooked.
To explain further, WordPress typically stores information (or data) on a MySQL database located on your web host. Storing information in a database, rather than in files, makes WordPress more flexible, capable and reliable. This is why most web based software use databases for information storage. Databases are not with out their drawbacks. It takes time to access a database, look up, retrieve it and display information. Typically the time required to perform these actions is negligible. The capabilities the database affords are worth the minor performance hit. Over time the database can become bloated however, this slowing down the entire site.
Not only are users intolerant of slow websites (many leave after 4 - 5 seconds of load time,) Google now includes site performance in their ranking algorithm. Suffice to say, you can no longer afford to have a slow website regardless of cause and many do because they have not kept their database healthy. To do so you must put your database on a diet, optimize your tables and eliminate the need for it altogether where possible.
Going on a Database Diet
The best way to address an "overweight" database is by slimming it down. This means optimizing the database by removing any old, unnecessary information (or tables.) You could login to your MySQL database and do this manually (if you are a gluten for punishment I highly recommend it,) but it's better to have a plugin do it for you. The choice is yours, I choose the later. The WP Database Optimizer plugin will delete excessive post revisions and other unnecessary information and you can even schedule it to run regularly.
This plugin takes care of obvious bloat, but misses the commonly overlooked pending comments. If you are like me you see pending comments much like e-mail, at a certain point it's not worth manually marking each one as spam which in turn causes them to build up to an excessive level. Those 5,000+ spam comments awaiting moderation are also slowing down your website (damn you spammers,) so you should probably clear them out. Of course WordPress doesn't make it easy to delete thousands of comments at the same time, so we will have to find a plugin to do it for us. Luckily the Delete Pending Comments plugin does just that. One click and some extra confirmation latter and your database is feeling lean and mean.
With your Database slimmed down you can move onto step two, optimize that sucka!
Optimize Your Tables
MySQL has an "optimize" function built in which defragments stored data. This reduces the time necessary to query a database and can reduce the file size as well. Again, for those of you who love pain you can do it manually... but I would much rather let the WP-Optimize plug-in do it for me. Beyond optimizing tables, WP-Optimize can also clean out the database making the two aforementioned plug-ins unnecessary. Three birds with one stone.
Now your database is lean and optimized, you should be good to go right? Not quiet, there is one last step. Eliminate the database where possible.
Don't Use the Database
There is no better way to reduce database associated slow downs than removing the database all together. Sound fanatical? Stay with me. I am not suggesting you find some way to run WordPress with out a database, rather use a caching plugin to reduce how often the database is used (my favorite is W3 Total Cache.) Caching plugins create a static version of your page the first time a user visits. Meaning, when the first users visits your page the database is accessed to generate the page. That generated page is stored on the server so the next time a user visits the page they are served that same pre-generated page and the database is left alone. When the page is altered, such as an edit or a new comment, the generate page is flushed and a new one is created.
This reduces most of the database associated slow downs. However, since some users will still encounter the database generated page it's worth getting your database trimmed and optimized as described above.
Summary: Set and Forget
The great thing about the plugins mentioned is most of them can be automated. Just install them once, schedule them to be run routinely and you have very little to worry about. It just takes the initial twenty minutes to get them installed and configured. Last but not least, the healthiest database is one with several backups. Make sure to backup your database frequently to prevent any unrecoverable issues.