I have a site where the options_id field in the wp_options table have grown very, very large. After a month and a half after the installation the auto_increment value is now at over 10 million.
While there are a number of plugins installed (such as ACF and W3 Total Cache) they do not seem to be the cause. They have been installed on others sites together with the same theme framework without any issues.
I have a local dev environment of the affected site where all the plugins and theme updates are first tested. It’s auto_increment value is only about 15,000 which I would expect given all the theme development on that install.
The site has also been very slow to load, at about 3 seconds per page if it wasn’t cached by W3 Total Cache (with page caching enabled the load times are great). Given the slow load times and the high auto_increment value there would seem to be some kind of connection between the two.
The options table itself only contains about a 1000 rows, which seems normal, so it is able to clean up after itself.
The site is part of a multisite network and each site in the network has at least an auto_increment value of 1 million or more.
All other tables in the database haven’t been affected, each of them show a normal number of rows given the amount of pages on each site.
Anyone got any ideas?
I had a similar issue on another site recently, memory usage was through the roof. When I checked the db, wp_options was about 80megabytes. Running this SQL reduced the entire db to 15mb:
DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient_%'
Obviously, replace wp_ with your table prefix.
the auto_increment value by itself is not important, but the fact that you got there indicates that you do a lot of option insertion either directly or more likely as a transients.
As said above, by itself that value is meaningless and even having 1k options should not impact the performance that much if they are auto loading. I would guess that your performance issue is unrelated to the options table size and might actually be cause by poor caching implementation that used transients, but might as well come from other areas of your code.