clean wp_options table unused rows

Question

In my Wordpress Blog site i have installed too much plugin and remove also many plugins. In database table "wp_options" size increases approx 13MB and there are many rows which are unused at this time. So I want to remove these unused rows from "wp_options" table and optimized this table as much as low size. But HOW?

Answer 1

There is a plugin called - Clean Options which promises to remove all unused entries from wp_options table. NOTE - that plugin hasn't been updated for 2 years, you must do backup before using it.

To remove all data manually

You have to manually look into database for unused tables or rows and use the SQL query to delete those permanently from the database.

• Most of the plugins create new tables with a custom prefix such as if you installed yoast seo plugin it will create tables with prefix _yoast_wpseo_ that makes easy to identify All the tables created by that plugin and If you're no longer using them you can drop them by using SQL command similar to that of given below.

    DELETE FROM wp_options WHERE option_name LIKE ‘_yoast_wpseo_%’;
  

Note

That SQL query will select all rows with prefix _yoast_wpseo_ (Note the '%') to delete from SQL table. Similar way you can delete tables created by other plugins too.

Answer 2

i guess you've already dug the path. from where you've seen that table is already taken 13MB? certainly from some GUI tools and possibly using phpMyAdmin. You can clean the unused rows from there.

1. Take a full backup of the current table. you can copy table using phpMyAdmin's operations tab.
2. then open wp_options table in phpMyAdmin and remove any suspecting rows.
3. after removing the any rows, check site to make sure you did not break anything. remember, you will break wordpress and/or any of its extensions (plugins/themes) if you remove any rows that are used by wordpress itself or its current installed extensions.

you can also check the disabled plugins which fields they added. if those plugins did not uninstall/remove those rows, you can delete it yourself and this way you create less risks of breaking stuffs.

Good luck

Answer 3

One of the blogs I manage experienced this problem. wp_option table loaded with GiB of non-required data. This happens few of the plugins insert metadata, notification data, etc...to wp_option, but won't delete those data. Some of the plugins are Woo Commerce, Monster Insights, Astra theme...(Sorry, if this bug is being resolved.)

To solve the problem:

1. First access phpmyadmin panel.
2. Go to SQL and run the following SQL Command:

   SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM wp_options WHERE autoload='yes'
   UNION
   SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes'
   UNION
   (SELECT option_name, length(option_value) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)
   

3. Look for the row which is consuming GiB of data and is unused.
4. Delete the row.

This solved the problem.

References: https://kinsta.com/knowledgebase/wp-options-autoloaded-data/