Fix lỗi WordPress wp_options table size too Big, and how to reduce it! Ecommerce

Fix lỗi WordPress wp_options table size too Big, and how to reduce it! Ecommerce




Recently one of our clients running WordPress with a bolt on Ecommerce plugin WP Ecommerce, was experiencing some issues with performance. We found the database had ballooned to 175mb, not overly big but huge for this particular client. So we looked at how to resolve and reduce the database size.
Upon investigating using PHP MyAdmin we found that the wp_options table was the main culprit 169mb. So to remedy this we first took a complete database backup before we started, in-case we needed to restore. After some further research we found this interesting blog post here, “Easily Reduce & Clean Size of Options Table in WordPress“, this lead us to our first step of running an SQL command to remove temporary entries that WordPress creates in the options table. These entries are transient entries that are usually created to simply store cached information.
Step 1

So in PHP MyAdmin we ran the command:


DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient_%');


After running the command approximately 220,000 rows were removed! Success, well nearly. We could see the rows had reduced but the table size had not budged, we also noticed in MySQL that the overhead size for that table was also at 156mb.
Step 2

So with a little bit more research we found that because of the huge number of rows removed we needed to perform a little database maintenance. So we ran a repair and optimize command on the database in PHP MyAdmin and hey presto our database had reduced from 175mb to 5mb and all was working fine.
We hope this helps any other people running WordPress in a similar situation.