WordPress is a dynamic blogging and content management system that relies on a database backend to function. All content, both from the site and from its users, is stored in a database. Over time that database can become cluttered and even slow. With a few optimization tricks, you can clean out a lot of the clutter and get your WordPress database up to peak performance.
Note: Before you begin making any changes to your WordPress database, you MUST backup everything. No matter how small you think a change may be, plenty of experienced users have suffered data loss because they did not make backups.
If you run a WordPress blog long enough, you are going to accumulate an alarming number of post revisions. Revisions to posts and their meta data can take up a great deal of space. This simple SQL query can remove them.
DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);
While pingbacks are nice, they can also take up a lot of space. If you want to remove them, run this query:
DELETE FROM wp_comments WHERE comment_type = 'pingback';
Alternatively, if you do not want to run manual SQL queries, you can use an optimization plugin like Wp-Optimize. This tool will help you optimize your database and remove any unnecessary or unwanted revisions, comments, spam, etc. Nevertheless, even if you use this plugin, you should still make backups. Always make backups, for everything – always.