[wp-hackers] Time for a wp_post_relationships table?

[Mike Schinkel]

On Aug 4, 2010, at 3:32 PM, Otto wrote:
> On Wed, Aug 4, 2010 at 2:08 PM, Mike Schinkel <mikeschinkel at newclarity.net> wrote:
>> A more performant way to do that would be to use a posts_join and posts_where hook to add a "related_to" and/or "related_from" arguments that appends the correct join and where clauses reducing to a single query what should be a single query.  Better yet would be to add wp_post_relationships to WordPress core and add those parameters into query() itself.
> 
> Ugh. No, man. Complex queries are the death of WP sites. Consider that
> in most of the webhosting in this world, database servers are shared
> amongst many websites. So you want to rely on the database as little
> as possible.
> 
> Two simpler queries are often preferable to one query which adds load
> to the database server. Why? Because the DB server is generally your
> major bottleneck. Loading any sort of work onto it with JOINs and the
> like causes you nothing but trouble in the long run.
> 
> Note, I'm not commenting on the specific code, just making a general
> observation. If I can do two or three queries with no joins, it's
> usually a heck of a lot faster than one complicated query with two or
> three joins. Less queries is not always better.


On the surface that goes against everything I've learned about databases and SQL in both developing and professional instruction over 20+ years but I'm not going to summarily dismiss it out of hand.  Can you provide any benchmarks or commentary from people at MySQL to show this to be true other and not just opinion?  Any other evidence?

That said, if it is true then you'd really want to avoid using the taxonomy system to link posts (because it requires 3 to 4 table joins) and instead prefer the proposed wp_post_relationships table I proposed (because it would require only 1 to 2 table joins), right?

-Mike