[wp-hackers] Database structure Wordpress

[Mike Schinkel]

On Dec 3, 2009, at 10:12 AM, Otto wrote:
> You're not going to get a whole lot of support for this idea. Mainly
> because it's been demonstrated that such a design is less efficient
> and slower than the current scheme of keeping things together.

I agree on this point because Matt is (sadly) adamantly against it.

> Let's say you have a table for each post type. What happens when you
> need to pull from more than one post type and interleave them together
> based on, say, date? Now you're pulling from two tables. And since you
> want metadata to be columns, they don't match each other either. So
> you need two SELECTs and then PHP code to mix them up. Whereas if you
> keep all posts in the same table with a post_type column, it's one
> SELECT and no special PHP.

However, you are making assumptions that are invalid; there are other ways to do it which is what but Drupal CCK and Pods for WordPress do; a central posts table and then specific tables for the unique fields needs for each post type. 

> Metadata as columns doesn't make any sense either.

I don't follow what you mean here.  

> The only reason to 
> have a column is if every post, or most of them, will have that data
> filled in. Also, having a column usually means pulling the data from
> that column every time, else you'd have to requery for it later
> anyway. And if you have to requery for it anyway, then it's more
> efficient to store the data in a separate meta table, using key value
> pairs. Indexing makes such a query faster, and the space requirements
> are much less since you don't have extraneous columns.

Actually, having tables like follow works quite well for performance:

SELECT p.*, c.* FROM wp_posts p INNER JOIN wp_custom_products c ON c.post_id=p.id


> It's all well and good to have good database design, but the
> difference is that good design does not equate to "faster" in real
> world applications. The goal of "high performance" is often in direct
> conflict with correct relational database design. 

Unless you are talking about things like Map/Reduce or simply carrying redundant data across several tables to improve performance, I'm afraid I'm going to challenge you for concrete examples here that are backed up with something other than opinion.

-Mike Schinkel