I just got done deleting a bunch of rows on a WordPress MySQL Database only to find that the database didn’t get any smaller! In my case, I deleted about 450,000 total rows, which is obviously a lot! For some reasons this install isn’t deleting wp_session entries in the wp_options table, and they had been accumulating for years. More on that when I figure out why…
Anyway, Google wasn’t particularly helpful in solving this, but the solution is so easy it will make you want to cry. If this wasn’t the first result you clicked on, I’m sure you waded through useless answers from hardcore database admins who can’t fathom an instance in which you would want to remove part of your database and hopefully make it smaller.
Just ignore those people.
Anyway, here’s how you actually shrink the database after deleting large numbers of rows.
Simply log in to phpMyAdmin, check the box next to the table you wish to optimize, scroll down to the dropdown menu, and select “Optimize Table”.
That’s it! You’re done! If you’re anything like me, all of the “Overhead” for the table will be gone, and the size will have dropped down to a tiny fraction of what it was. Obviously this will depend on what you’ve deleted, but for me, 450,000 worthless rows in wp_options means that I’ve shrunk quite a bit!
Good luck!
Special thanks to wpmudev.org for their optimization guide that contained this gem of information, and whose screen cap showing the optimize function I shamelessly commandeered (original here).
Also thanks to fastsecurecontactform.com (site no longer active) for their advice on removing the _wp_session rows. It’s not very hard and I just followed their instructions exactly, though instead of deleting 500 rows at a time I did 50,000! My server didn’t have a problem but yours might.
