[wp-hackers] Database structure Wordpress

[Mike Schinkel]

On Dec 3, 2009, at 1:04 PM, Otto wrote:
> On Thu, Dec 3, 2009 at 10:55 AM, Mike Schinkel
> <mikeschinkel at newclarity.net> wrote:
>> I agree on this point because Matt is (sadly) adamantly against it.
> 
> I used to agree with you.
> 
> Then I worked with it for a while and built some custom systems based
> on similar principles.
> 
> Now I agree with Matt. :)

In general I'll agree multiple tables add complexity. OTOH there are certain relationships and certain queries that simple can't be done with metadata.

>> 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.
> 
> How is this different than the existing metadata system, really? We
> have postsmeta which can hold arbitrary fields associated with
> arbitrary posts.

There as some queries that get too complex to be handled with metadata.  Each field that you bring into a SQL join requires joining another table and every table you join slows the query done.  If you have grandparent-parent-child relationships it can get even more complex.  At a certain point you hit the limit of MySQL query analyzer.

>> 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
> 
> Again, we have this. See postmeta, commentmeta, usermeta, etc.

And see my comments about how that becomes overly complex when you have needs for complex joins (like I've had in several projects the past couple years, one I did in Drupal.)

>> 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.
> 
> Well, simply think about it a minute. Joining tables to obtain data is
> slower than not joining them, obviously. So for something like the
> posts table, you really only want data in it (as columns) that you
> will always need, every time. Author, for example, is something you
> usually will display on the page, so you really always needs it and it
> makes sense to retrieve it, every time.

I agree completely there.

Where I have an issue is when you are talking custom fields.  If you have ten custom fields for a post then it is much slower to load them from a metadata table than from a joined table where the joined table has actual fields for all custom fields.  

With the metadata table approach you either join and get back ten copies of the post body (which is *really* slow), and you have to loop thru in PHP code ten times, or you make two queries and have to loop through PHP code tens times (and two queries are slower than one.)

> But, you don't always need custom_data=bob, do you? So instead of
> mixing that into the main posts table, it's better to have it as a
> meta value in a separate table. Indexed on the post id and the key
> name, for quick lookups. Then, you can get that meta data only when
> the program actually asks for it, on demand, sort of thing. Saves you
> a lot of time since you're not getting it every time. If you were to
> merge it into the main table, then you'd spend a lot of time getting
> "custom_data = null" back from the database, when you didn't care
> about the custom_data column in the slightest.

I agree you do not merge into the main table. That's why you put it into a wp_custom_products table instead of in wp_posts.

In this, I think there should be moderation in all things.  IOW, ideally WordPress would have the option to use metadata for most use cases but also be able to use separate tables when the user and their use cases demand it.

-Mike