Improve Database Performance by cleaning WP_OPTIONS Table

Today I am discussing how we should clean up WP_OPTIONS table in WordPress for old and big websites. This is the most important area which we neglect when we are improving database performance. Checkout the following tips to check, clean and optimize your WordPress WP_OPTIONS table.

Improve Database Performance

The WP_OPTIONS table possesses all information about your WordPress website for e.g. see below:

• Website main URL, admin email, default category, posts information and other important info.
• Settings for plugins and themes (Most Important)
• Caching data

Following is one of the most important field which we completely neglect when improving database performance:

Most important thing to understand about WP_OPTIONS table is the autoload field. This field has either YES or NO as value, meaning ROWS with YES as value in it will auto load the script in all pages of the website loaded by wp_load_alloptions WordPress function.

Sometime plugins or themes are just activated/uploaded for testing purpose but their options remain in the table. Meaning each time website loads unnecessary data is queried. Plugin and theme developers are often loading data into the WP_OPTIONS table instead of creating their own tables.

Idealy WP_OPTIONS table should not exceed more than 1mb this can vary obviously depend on the size of the website you are running. If it’s more than 3-5mb and your website is not big enough and just a simple blog than this is the time you should start optimizing WP_OPTIONS table of your WordPress website. In above screenshots my table prefix is WP_SIMS_ and not WP_ this is just because I have added an extra layer of security that is completely a different story which I will discuss later but I just wanted to give you guys a heads up about the screenshots.

Following is MYSQL query which you can run in PHPMYADMIN to check size of Autoloaded data:

SELECT SUM(LENGTH(option_value)) as autoload_size FROM wp_sims_options WHERE autoload='yes';

See below results of the query in screenshot:

Autload_size is returned in bytes. There are 1000 bytes in a KB and 1000 KB’s in a MB (Obviously if you are reading this article than I am 100% sure that you know the calculation but I wrote just in case you don’t know that results are in bytes)

In above case WP_OPTIONS table is only 0.337mb which is ideal and does not need any improvement in database performance. Let’s discuss a case where we have large autoloaded data. Here is a SQL command which will sort the TOP 10 items by size:

SELECT option_name, length(option_value) AS option_value_length FROM wp_options WHERE autoload='yes' ORDER BY option_value_length DESC LIMIT 10;

After running above query you may see plugin/themes related values which may be not available in your website any more. You can delete all those options, let me give you an example of JETPACK plugin. After running following query, we found out that one of our website had extra data about JETPACK which was not used any more in the website:

SELECT *
FROM `wp_options`
WHERE `autoload` = 'yes'
AND `option_name` LIKE '%jetpack%'

So after selecting all the ROWS simply hit delete because JETPACK is not being used any more in the website. (Make sure you have basic information about running MYSQL commands before performing anything or you can download your database before deleting any of your rows in table).

I hope today’s article may have helped you to improve database performance of your website. If you are not tech-savvy and need more help to have your online web presence ready to rock feel free to comment below or CONTACT US

Recommended Reading: Meet the New WordPress: Top Features & What to Know Before Upgrading

If you are not tech-savvy and looking to get some tech help for your website, contact us for Affordable Web Development Services