Following my move to Linode, I’ve been reviewing my WP installation with the aim of streamlining the size of the database, increasing the speed of the site and reducing the amount of time needed to back both the directory and database. Here’s what I did.
First the easy thing, I deleted all of those unnecessary plugins. I removed five unused and obsolete plugins which were simply taking up space.
One plugin I binned was a rather an ineffectual caching plugin. I tried a couple of replacements but found that W3 Total Cache which came highly recommended, seemed best for my set up. Subjectively at least, viewing pages feels faster.
Next I turned my attention to the database. My wp.sql file was 60MB before I started tweaking. Looking at the tables in phpmyadmin, I saw that wp_statpress and wp_posts were the largest components. I found and ran the commands to optimise those two tables. Obviously before doing any work on an important database, make a back up – or two!
Login to your database: # mysql -u root -p databasename
Then run the commands:
OPTIMIZE TABLE wp_posts;
OPTIMIZE TABLE wp_statpress;
You’ll see an output similar to this each time you run the command.
mysql> OPTIMIZE TABLE wp_posts;
| Table | Op | Msg_type | Msg_text |
| wp.wp_posts | optimize | status | OK |
1 row in set (0.16 sec)
If you you see “Table is already up to date” instead of OK the table is as optimised as it can be.
The above process may optimise the database from the MySQL server’s point of view but it didn’t’t reduce the overall size . To achieve that, removing the copies WordPress makes of each post revision had the largest effect. Again from the MySQL command line run:
DELETE FROM wp_posts WHERE post_type = “revision”;
I executed the above and then repeated the optimise table command.
As a result, the total wp.sql file shrank from 60MB to 45MB which is impressive. There are some WP plugins available which can also assist with “weight loss” but as the above commands are so quick and effective, I didn’t see a need to try them.
Finally, you can amend your wp_config file to prevent WordPress retaining say more than 3 revisions per post. Personally, I’d rather have the option of as many revisions as needed and occasionally perform the above steps when the DB becomes bloated.