[wp-hackers] Wordpress as a CMS

[Mike Schinkel]

On Dec 21, 2009, at 9:34 AM, Brad Williams wrote:
> I fail to see the difference between storing your data in a meta table vs. a
> custom table?  After a few thousands records wouldn't your custom table
> suffer the same consequences as a meta table?  Ultimately it comes down to
> your server setup and what it can handle, not what table you store the data
> in.

Relational databases were optimized to work relationally not key-value pairs.  Index performance slows down when dealing with large numbers of records and the meta table approach typically adds an order of magnitude more records. Each meta value is another meta record you have to loop through to collect the data for a post. With a high traffic website this might be significant.

Meta records also store their "structure" with each record, i.e. the "key" in the "key-value" pair.  Relational tables store the structure only once so meta records are much more space efficient, especially if the records are using integer primary and foreign keys.

Another consideration is that certain types of join queries are much more complex or even impossible with meta tables. Let's say you want to do a query for posts that represent real estate properties and have the three meta values type, price, and city:

SELECT 
	post.ID, 
	post.ID, 
	post.post_title, 
	type.meta_value AS type, 
	price.meta_value AS price, 
	city.meta_value AS city
FROM 
  wp_posts post 
  INNER JOIN wp_postmeta city ON city.post_id = post.ID
  INNER JOIN wp_postmeta price ON price.post_id = post.ID
  INNER JOIN wp_postmeta type ON type.post_id = post.ID
WHERE 
  city.meta_key='city' AND city.meta_value='Atlanta' AND 
  type.meta_key='type' AND type.meta_value='condo' AND 
  price.meta_key='price' AND price.meta_value<250000

That's four joins with 6 criteria on string keys and a more complex query.  Now let's look at a custom table approach:

SELECT 
	post.ID, 
	post.ID, 
	post.post_title, 
	prop.type, 
	prop.price, 
	prop.city
FROM 
  wp_posts post 
  INNER JOIN wp_custom_property prop ON prop.post_id = post.ID
WHERE 
  prop.city.meta_value='Atlanta' AND 
  prop.type.meta_value='condo' AND 
  prop.price<250000

That's two joins with 3 criteria on 2 string keys and one numeric key on a simpler query.  Not only is the query much easier to read thus easier to maintain but it also will perform a lot better for a large number of records.

Now, I haven't even started to explain how ugly it gets if you need to have multiple levels of relationships, i.e. if you had a "post type" of "City" and you were instead doing the same query but this time instead of for city name you used city population greater than 5 million.

Does that maybe explain why there really is a difference between the two architectures?  

Point of note: What I'd really like to see is to have meta tables available for the simple cases and for sites that don't expect a large number of records or custom fields but the *option* built into WordPress to switch to relational tables when needed, with migration tools between the two.

-Mike Schinkel