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).

Following are the mysql queries shown in Video to optimize the database:

-- This query will show all meta data for comments that do not have a comment anymore
SELECT * FROM your_prefix_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM your_prefix_comments );

-- This query will delete all meta data for comments that do not have a comment anymore
DELETE FROM your_prefix_commentmeta WHERE comment_id NOT IN ( SELECT comment_id FROM your_prefix_comments );

-- The query is useful for finding comments that have been incorrectly flagged as spam by Akismet.
SELECT * FROM your_prefix_commentmeta WHERE meta_key LIKE '%akismet%';

-- This query will delete all meta data for comments that have been flagged as spam by Akismet.
DELETE FROM your_prefix_commentmeta WHERE meta_key LIKE '%akismet%';

-- Finding post meta with NULL values
SELECT * FROM your_prefix_postmeta pm LEFT JOIN your_prefix_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

-- Deleting post meta with Null values
DELETE pm FROM your_prefix_postmeta pm LEFT JOIN your_prefix__posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

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