[wp-trac] [WordPress Trac] #14513: Time for a wp_post_relationships table?

[WordPress Trac]

#14513: Time for a wp_post_relationships table?
-----------------------------+----------------------------------------------
 Reporter:  mikeschinkel     |        Owner:                
     Type:  feature request  |       Status:  reopened      
 Priority:  normal           |    Milestone:  Future Release
Component:  Post Types       |      Version:  3.0.1         
 Severity:  normal           |   Resolution:                
 Keywords:                   |  
-----------------------------+----------------------------------------------

Comment(by mikeschinkel):

 Replying to [comment:27 scribu]:
 > Replying to [comment:26 mikeschinkel]:
 > > you are still ignoring the problem of posts getting renamed by users
 that have mirrored terms that are shared by other taxonomies.
 >
 > I thought we covered that. Why do you keep bringing up an argument that
 has already been addressed?
 Replying to [comment:28 scribu]:
 > Oh, and yeah, I looked at your code on gist and it's NOT similar to what
 I'm doing.

 The reason I kept bringing it up was that I misunderstood; I thought you
 were essentially the same as what I was doing. You  are correct, '''I
 apologize''', is it NOT similar.

 However, not only is your solution NOT similar, it is ''worse''. When you
 boil away all the other stuff the problem is that you are overloaded a 200
 character text field to store a 20 byte key.  It's actually a cool little
 hack, but it doesn't scale and shouldn't be recommended.

 When I run EXPLAIN on this simply query:
 {{{
 mysql> EXPLAIN SELECT term_id,name,slug FROM wp_terms WHERE slug='p24';
 }}}
 This is what we get (I don't know why MySQL makes the key 602 bytes but
 while it's not as bad as trying to key off a longtext field, it is close):
 {{{
 *************************** 1. row ***************************
 id           : 1
 select_type  : SIMPLE
 table        : wp_terms
 type         : const
 possible_keys: slug
 key          : slug
 key_len      : 602
 ref          : const
 rows         : 1
 Extra        :
 1 rows in set (0.00 sec)
 }}}
 Your code ultimately runs this (assuming it is not cached) which is
 basically the same thing:
 {{{
 SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON
 t.term_id = tt.term_id WHERE tt.taxonomy = 'p2p' AND slug = 'p24' LIMIT 1
 }}}
 The above code is not so bad if there are <1000 or even <2500 records in
 wp_terms but if we have 10,000 records (movies) with 10 relationships a
 piece (actors) this absolutely does not scale. (And to make matters worse,
 your code current does not recognize the reciprocal relationship when one
 is encoded so a user would have to do both tasks: 1.) Add Actors to a
 Movie and 2.) Add Movies to an Actor which is crazy.

 So I can't endorse your implementation except for use on a site that will
 never have more than a small number of posts.

 Heck, if I were going to hack the taxonomy system and use it in a non-
 standard way that could cause confusion, I'd add an index for
 "`term_group`" and the store foreign key values for `$post->ID` there.
 Then you'd at least be to relate to 8 bytes keys with an index with an
 EXPLAIN that looks like this:

 {{{
 mysql> EXPLAIN SELECT term_id,name,slug FROM wp_terms WHERE term_group=24
 \G
 *************************** 1. row ***************************
 id           : 1
 select_type  : SIMPLE
 table        : wp_terms
 type         : ref
 possible_keys: term_group
 key          : term_group
 key_len      : 8
 ref          : const
 rows         : 1
 Extra        :
 1 rows in set (0.01 sec)
 }}}

 Replying to [comment:29 Denis-de-Bernardy]:
 > Replying to [comment:18 mikeschinkel]:
 > Well, if you "must" know...

 I've been interested in much the same but have never dug deep so I'll
 defer to your research and judgement.  Frankly I'd have to really think
 about what you posted at length and right now I'm too tired.  What's
 important to me on this ticket is 1.) that we recognize that post-to-post
 relations are a common and important use-case and 2.) that we don't accept
 a solution that is impractical and/or doesn't scale simply because nobody
 stood up to challenge it.  Of course I'd like to support get added to core
 but frankly I knew that it might be an uphill battle so I'm prepared for
 that not to happen.

 So in the spirit of #2 another option could be to add an indexed field to
 postmeta (maybe "`meta_int`") that can be used to start bigint values in
 (like post_ids) and/or anything else that can be serialized into a big int
 (i.e. time, etc.)  If it is `null` then is should behave in a backward
 compatible manner. If it is not null then we could look to meta_value for
 the "type" (post, date, date/time, etc.)  Just a thought...

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