[wp-trac] Re: [WordPress Trac] #5183: General Meta-Data Table

[WordPress Trac]

#5183: General Meta-Data Table
-----------------------------------------+----------------------------------
 Reporter:  filosofo                     |        Owner:  anonymous     
     Type:  enhancement                  |       Status:  reopened      
 Priority:  normal                       |    Milestone:  Future Release
Component:  General                      |      Version:  2.9           
 Severity:  normal                       |   Resolution:                
 Keywords:  needs-patch meta-table meta  |  
-----------------------------------------+----------------------------------

Comment(by Denis-de-Bernardy):

 Replying to [comment:31 mikeschinkel]:
 > > When it comes to properly optimize the queries afterwards, joining
 posts with a table contains IDs from posts, comments, links and users
 won't fare as well as one on a table that contains IDs from a single one.
 >
 > Agreed. My biggest concerns are making sure there is what I like to call
 "an escape valve"; i.e. the ability for a plugin developer to add metadata
 for any arbitrary object w/o having to add their own table or to overload
 an existing table in a non-relational manner. If the team thinks link meta
 should be in a specific table I'm fine with that but do want to see a
 table that can handle arbitrary objects.

 I think the real underlying issue is we need abstraction in the php. It's
 like, if the data cannot go into wp_posts (which could really be called
 wp_nodes), and ends up requiring a separate table, I'd personally suggest
 it should get its own meta table as well. So long as the needed php
 functions are around to manage this user-defined meta table, things should
 be fine.


 > > Now, if we introduce some kind of node ID so as to ensure no two
 pieces of data in posts, comments, links and users share the same numeric
 ID, that's a different story. Generic meta and term2node tables then makes
 perfect sense. But that's a much more massive change.
 >
 > I'd caution against a generic node ID; I think it adds too much
 complexity w/o enough benefits.  I also think that there is huge value to
 established structure and changing established structure should only
 happen when very significant improvements would come with it.

 I know... I've been playing around with the idea of a nodes table for over
 10 years. We could actually do this by switching to UUID types instead of
 auto-incremented integers. It adds an extra benefit along the way, too:
 you can then sync sites that use shared databases.


 > Simply put: 15 years ago I was exactly certain the answers for all the
 rules about how database apps should be developed. Today I wonder if there
 really are any rules at all that don't fail in one use-case or another.

 Might it be that you only ever needed to write queries that involved one
 or two joins? I ask, because my own 10 years of experience tell me a
 rather different story. I was a varchar index junkie until 2-3 years ago
 -- until I ended up needing to look into the performance of an app that
 was heavy on joins and that had more than a minute amount of data.

 PGSQL ended up using merge join plans (= the slowest you can get) that
 involved zillions of rows. No amount of vacuum analyze, adding partial
 indexes, or clustering the data helped in any meaningful way. Re-
 engineering the DB in such a way that PG had means to fetch the needed
 rows using less joins, on integer/uuid fields only, bought the app was
 brought back to life.

 My own bottom line was this: primary key (object_id, object_type) = avoid
 like the Plague if you've a large amount of data. If you can ensure that
 object_id is unique across the various tables using a single sequence (and
 thus the primary key all by itself), you can abstract as much as you want
 (and indeed, you don't need the hard-coded referential integrity). But it
 *must* be unique.

 In case you're thinking WP will never get so much data that this will make
 a big difference, consider the zillions of blogs, users, posts, terms,
 links, and comments on wordpress.com.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/5183#comment:32>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software