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

WordPress Trac wp-trac at lists.automattic.com
Tue Jun 16 22:33:25 GMT 2009


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

Comment(by Denis-de-Bernardy):

 More reference, since IRC logs are broken:

 {{{
 well, I don't think there's anything about the wp_terms table structure
 that prevents different terms from having the same name.  that's an api
 issue
 11:53 PM ddebernardy
 no no, it's a db issue underneath
 11:53 PM filosofo
 The foreign key thing could be solved with another table, wp_objects
 11:54 PM ddebernardy
 that's even worse than our current implementation
 11:54 PM
 and it won't allow the foreign key
 11:54 PM filosofo
 ?
 11:55 PM ddebernardy
 well, unless you're meaning wp_objects is merging posts and links (heck,
 why not...), it's really not a good idea, because you'd merely add an
 extra table in the lot that doesn't fix the foreign key constraint problem
 11:56 PM filosofo
 right, that's what I mean.  all objects in one table
 11:56 PM ddebernardy
 FWIW, I've been playing with that idea fo the past 10 years or so
 11:56 PM
 the only implementation I found worthy of implementing was like this:
 11:56 PM
 table nodes (id)
 11:56 PM
 table foo (id references nodes(id))
 11:57 PM
 that way you're working on native structures
 11:57 PM
 and you still have native indexes
 11:57 PM
 but it actually comes down to lackings in database engines
 11:57 PM
 because what the above really should be is:
 11:57 PM
 table nodes (id)
 11:57 PM
 table foo inherits nodes
 11:58 PM
 with id shared across the board
 11:58 PM
 you can do that with a uuid field, basically. but your meta, e.g. created,
 modified, etc. then needs to be redefined in each table
 11:59 PM ruslany__ has left IRC ("Leaving")
 11:59 PM ddebernardy
 in pgsql, inherit won't enforce unique indexes. and I'm not even aware of
 a similar keyword in mysql
 12:01 AM filosofo
 I was going to say, I didn't think you could do that in MySQL.  I don't
 think foreign keys are supported with all mysql storage engines.
 12:01 AM ddebernardy
 no, they're not. only in innodb, ndb, and a few more (but not myisam)
 12:02 AM rodrigo_sampaio1 has left IRC ("Leaving.")
 12:02 AM ddebernardy
 still, it's generally good to be able to enforce them. it means the db is
 well designed if you can, and I'd like to make it possible to enforce
 transactions in 2.9 or 3.0.
 12:04 AM filosofo
 what about the db structure prevents different terms from having the same
 name?
 12:04 AM ddebernardy
 UNIQUE KEY slug (slug),
 12:04 AM
 in wp_terms
 12:04 AM filosofo
 ok, missed that.  So change that.
 12:05 AM ddebernardy
 no no, you want more than that, really
 12:05 AM
 by the same token, then other fields would want to be in wp_term_taxonomy
 12:05 AM
 with a unique index on slug/parent/taxonomy
 12:05 AM
 and the term table then disappears
 12:06 AM
 for the sake of marking the change, I'd suggest renaming the table as
 wp_taxonomies, so that old plugins end up miserably failing (or then, we'd
 add a pseudoview to make it backwards compat)
 12:07 AM
 I'd be curious to have the thoughts of rboren on all of this, though

 12:11 AM sorich87 has joined the channel.
 12:13 AM filosofo
 so how about 3 tables: terms, taxonomies, objects
 12:13 AM
 we could move taxonomy definition into the db
 12:13 AM ddebernardy
 why terms and taxonomies?
 12:14 AM
 if you've an index on slug, held in taxonomies, then you can join that
 table on itself using ( slug ) where t1.id <> t2.id, and you've the same
 thing as terms
 12:14 AM
 the ultimate difference is, with three tables, you've three joins, with
 two tables, you've only two :-)
 12:14 AM
 and when you join tax join tax join tax as I and others do in a few
 plugins, it can make a huge difference
 12:15 AM
 query optimizers are generally lost after 8-10 joins and end up using a
 (slow) genetic algorithm to optimize query plans
 12:15 AM filosofo
 ok, but wouldn't there be a performance benefit?
 12:15 AM sivel[a] is now known as sivel
 12:16 AM filosofo
 currently, to query a taxonomy you have to do a text search on varchar
 12:16 AM
 with a taxonomy table, you could make that an integer
 12:16 AM ddebernardy
 oh, that's what you mean
 12:16 AM filosofo
 db size would be smaller, too
 12:17 AM ddebernardy
 in this case yeah, agreeing with you, but the general trend in WP is to
 use php arrays for this kind of stuff
 12:17 AM
 I mean, we're using a serialized option to store roles :D
 12:18 AM filosofo
 I think everyone would like to move away from that
 12:18 AM
 the roles, I mean
 12:18 AM ddebernardy
 yeah, I hope I'll find enough time to get this done in 2.9
 12:19 AM
 but there are still a couple of things we'll want in varchar fields even
 if we do that, for the same of making thing pluginable
 12:19 AM
 take roles, for instance
 12:19 AM filosofo
 so a role is taxonomy
 12:19 AM ddebernardy
 by the same token as the taxo table you're describing, we'd want roles and
 caps defined in tables with ids references in a user2role table
 12:20 AM
 trouble is, this makes heavy queries
 12:20 AM
 in practice, it's better to have an enum field to store roles, and caps
 12:20 AM
 enum is really an int with another name
 12:20 AM
 now, to make the thing extendable, we actually want a varchar in there
 12:20 AM
 and the same holds for taxonomies/terms
 12:21 AM
 whatever we do, we'll end up with a varchar field to make things
 extendable
 12:21 AM
 and end up with the thingy containing post_tag, category, link_category
 (and cities, countries, and whatever plugin authors want it to contain)
 12:22 AM filosofo
 problem with role as enum is that it doesn't allow easy custom roles,
 right?
 12:22 AM ddebernardy
 exactly
 12:22 AM
 hence the need for a varchar in WP, even if it performs more poorly
 12:23 AM filosofo
 hmm, but see what I meant before is that in your wp_taxonomies table...
 12:23 AM ddebernardy
 yes?
 12:23 AM filosofo
 you'd have to query over zillions of varchars, instead of just a few in a
 taxonomy table.
 12:24 AM ddebernardy
 no no, you'd still have a single one - to fetch post_tag, or category, or
 link_category, or...
 12:24 AM
 picture it as the current term_taxonomy and terms combined
 12:24 AM filosofo
 right, if we combine present terms and taxonomy....
 12:25 AM ddebernardy
 and renamed for the sake of making things backwards compatible (we'd make
 subqueries or views available for plugins that dig straight into it)
 12:25 AM filosofo
 then if we want to query for the "post_tag" "my term", we have to query
 over the large terms table looking for "post_tag"
 12:26 AM ddebernardy
 it would contain the same number of rows as the current term_taxonomy
 table
 12:26 AM
 it would merely have a few extra fields to play with, avoiding a join, and
 adding for the possibility of extra indexes
 12:26 AM filosofo
 yes, that's why I think we need a taxonomy table.
 12:27 AM ddebernardy
 issue with a taxonomy table (as you're thinking, referencing what really
 ends up being an array in WP, or so I'm understanding), has to do with the
 register_taxonomy function
 12:27 AM
 I suspect
 12:28 AM filosofo
 just make register_taxonomy like add_rewrite_rule
 12:28 AM
 a one-time thing
 12:28 AM ddebernardy
 works too, but we end up breaking plugins in the process, if terms becomes
 a different animal
 12:29 AM

 filosofo
 And this would improve the query_posts queries,
 12:30 AM
 because you could do one query
 12:31 AM ddebernardy
 correct
 12:31 AM
 hence the need for rboren feedback
 12:31 AM
 I mean, I'd be all +1 to break existing plugins and change the semantics
 of wp_terms
 12:32 AM
 but one of the core devs may end up wanting to keep backwards compat
 }}}

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


More information about the wp-trac mailing list