In one of the previous articles we wrote that some WordPress developers store default values that should not be in the database. It doesn't make sense to store 0 in a database. The WordPress plugin can pull the information from the database and if it's an empty value to treat that as 0.

Why are empty values a problem?

Well, it’s yet another record in the table that should not be there. Maybe database service optimized for empty fields but still to me it makes sense to not store those values. For example in our recent projects the post meta table had about 250,000 rows. There were about 50,000 empty rows.

What fields are considered empty?

By empty fields we assume that values that are either empty text, 0 or null. For now we won’t consider the text: false empty but it’s very tempting.

Before you delete or modify anything !!Backup!! always do so and use 2-3 methods just in case. We are not responsible for any data loss.

The idea for the post is to optimize your post meta table so it can be searched quicker but there could be poorly written plugins that may break when they don’t find the value even if it’s empty.

The following MySQL/MariaDB SQL queries will show you how many empty values are stored in the post meta table.

They need to be run into your favorite Database Management tool. You can use phpMyAdmin or Adminer or whatever you like.

Assuming that your table prefix is the default one: wp_
If you're using a different one update the queries as needed.

List all the rows that have meta_value column as empty or null values.

SELECT * FROM `wp_postmeta` WHERE meta_value='' OR meta_value IS NULL

Delete all rows that have null or empty values.

DELETE FROM `wp_postmeta` WHERE meta_value='' OR meta_value IS NULL

List all rows that have meta_value field column as 0.

We’re using a separate query because the database may convert the columns’ text value to 0.

You want all the columns that store exactly the number 0. No more, no less.

SELECT * FROM `wp_postmeta` WHERE meta_value REGEXP '^[0]$';

Delete all rows that have meta_value field column as 0.

DELETE FROM `wp_postmeta` WHERE meta_value REGEXP '^[0]$';
Do you use test/staging sites? Visit qSandbox for more info.

Disclaimer: The content in this post is for educational purposes only. Always remember to take a backup before doing any of the suggested steps just to be on the safe side.
Referral Note: When you purchase through a referral link (if any) on this page, we may earn a commission.
If you're feeling thankful, you can buy me a coffee or a beer