The way WordPress decided to deal with adding custom pieces of data is both cumbersome and elegant. Because it stores ALL metadata in one table, it can be painful to try to figure out what a data object looks like. Things like adding Foreign keys and enforcing certain relationships can not be done easily.

Your new BFF: wp_postmeta.

In WordPress, Custom fields do not actually exist until you add data to them. That means getting all the items that do NOT having some data can get tricky. The reason for this, is that posts do NOT have a structure you can reliably depend on, like a traditional programming object model. Rather, custom meta was tacked onto WordPress as a bit of an afterthought.

The Car example

Let’s imagine you have a custom post type called Car and you want to have custom fields like color, towing_capacity, fuel_type, and battery_capacity. If you were building a database of Cars, or building a list of Car objects in code, you might add a Tesla model 3 and picture this:

Name (post_title)	Tesla Model 3
color	black
towing_capacity	2000 lbs
fuel_type	electricity
battery_capacity	62 kwH

You might expect a 1999 Dodge Neon might look like this:

Name (post_title)	Dodge Neon
color	Lime
towing_capacity
fuel_type	gas
battery_capacity

However, in reality, in the WordPress database, the Dodge Neon looks more like this: ¹

Name (post_title)	Dodge Neon
color	Lime
fuel_type	gas

The towing_capacity and battery_capacity fields are not added at all in the database to the Neon. You never added the content, so nothing gets added to the wp_postmeta table. 

So what? Why does this matter?

This sounds great and efficient, and from a database size perspective, it is. However, if those fields don’t exist, how do you find all the cars that can’t TOW? Or what if you want to find all the cars that use diesel but have no towing capacity? Often, a WordPress developer will do one query to get ALL the car records, then get call get_postmeta on each entry. If you have 100,000 cars in your database, that’s 100,001 calls to your database.

A database administrator (DBA) would look at this issue and tell you that it can be done in one SQL statement. If Cars had their own structure and table, you could do something like this:

SELECT ID,fuel_type 
FROM wp_cars 
WHERE towing_capacity = '' 
AND fuel_type = 'diesel'

Simple. Easy to understand and read. However, WordPress puts all the postmeta into one table so to do it efficiently in ONE query, you have to do something like:

SELECT p.ID, m.meta_value AS fuel_type
FROM wp_posts AS p
INNER JOIN wp_postmeta AS m on m.post_id = p.ID
WHERE p.post_type = 'car'
AND m.meta_key = 'fuel_type'
AND p.ID NOT IN (
    SELECT DISTINCT post_id
    FROM wp_postmeta 
    WHERE meta_key = 'towing_capacity'
    )

This is actually two separate queries in the database as we’re using a subquery, but it’s one database call and a lot more efficient than making 100,001 calls to the database. It’s also a LOT harder to understand and read, which is why many WordPress developers will typically just loop through the items and use the built-in meta functions, despite its inefficiency.

While it’s true that it’s a drawback, structuring the WP database this actually offers much better flexibility overall. But I’ll save why it is a good thing for a different post.