Pruning wordpress revisions with the mysql command line

Ok, so you have updated your posts from all of your devices and now you have over 50 revisions of the page. You are never going to look at those revisions because you are the only editor on the site. Here is how you prune those unnecessary rows.

Create a pagodabox tunnel

First you need to tunnel the pagodabox db1 virtual-machines port to your local box.

$ pagoda -a [appname] tunnel [component] 
# Component id example db1

Once you have your ssh tunnel created, you should have a port [3307] forwarded to your local machine. Now you simply need to log into your mysql database like it was a local database on your machine.

$ mysql --host 127.0.0.1 -P 3307 --user  --database wp-db  -p

Finally, prune the data.

mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
|      686 |
+----------+
mysql> DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'

mysql> select count(*) from wp_posts;
+----------+
| count(*) |
+----------+
|      124 |
+----------+

Reference :