[wp-hackers] Creating Custom Taxonomies on the fly

[Mike Schinkel]

On Jan 30, 2011, at 1:35 AM, Dion Hulse (dd32) wrote:
> I'll be honest, I'm not a SQL performance nut, and I hate data duplication.
> I've just grabbed a SQL in use on a reasonably -low traffic- site which has about 200 terms in total and a few hundred posts, the result of this is transient cached(which is cleared when tags/cats change) and the site uses super cache as well, So the site has never had any performance issues or scaling problems due to this - Thats not to say it won't in the future of course.
> 
> SELECT DISTINCT t.*
> FROM wp_terms AS t
>  JOIN wp_term_taxonomy AS tt ON ( t.term_id = tt.term_id AND tt.count >0 )
>  LEFT JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
> WHERE tt.taxonomy = 'post_tag'
> AND tr.object_id
> IN (
>  SELECT tr.object_id
>  FROM wp_term_relationships AS tr
>    LEFT JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
>  WHERE tt.term_id = 3 # This is the Term ID of the category (or any related taxonomy really) that you want to intersect the tags with
> )
> ORDER BY t.name ASC
> 
> I'm sure it's not the best query, it could certainly be optimized (I'm pretty sure it's possible to get rid of that sub select, that'll choke when there are hundeds of posts in a category), but my main reason for digging it up, is the point out that you don't have to load all the posts up, just the ID's of the posts, which is stored in the taxonomy tables anyway.
> 

You are right, it's not very optimized. You really don't need dependent subqueries[1] like that if you can at all help it.  This unravels them:

SELECT DISTINCT 
	wp_terms.*
FROM wp_terms 
	INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
	INNER JOIN wp_term_relationships ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
	INNER JOIN wp_term_relationships intersected_term_relationships ON wp_term_relationships.object_id = intersected_term_relationships.object_id
	INNER JOIN wp_term_taxonomy intersected_term_taxonomy ON intersected_term_relationships.term_taxonomy_id = intersected_term_taxonomy.term_taxonomy_id
WHERE 1=1
	AND wp_terms.term_id<>intersected_term_taxonomy.term_id
	AND wp_term_taxonomy.count > 0 
	AND wp_term_taxonomy.taxonomy = 'post_tag'
	AND intersected_term_taxonomy.term_id=3
ORDER BY 
	wp_terms.name ASC

Of course if you are going to use it in PHP for WordPress this would be more useful:

  global $wpdb;
  $sql =<<<SQL
SELECT DISTINCT 
	{$wpdb->terms}.*
FROM {$wpdb->terms}
	INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->terms}.term_id = {$wpdb->term_taxonomy}.term_id
	INNER JOIN wp_term_{$wpdb->term_taxonomy} ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_taxonomy}.term_taxonomy_id
	INNER JOIN {$wpdb->term_taxonomy} intersected_term_relationships ON {$wpdb->term_taxonomy}.object_id = intersected_term_relationships.object_id
	INNER JOIN {$wpdb->term_taxonomy} intersected_term_taxonomy ON intersected_term_relationships.term_taxonomy_id = intersected_term_taxonomy.term_taxonomy_id
WHERE 1=1
	AND {$wpdb->terms}.term_id<>intersected_term_taxonomy.term_id
	AND {$wpdb->term_taxonomy}.count > 0 
	AND {$wpdb->term_taxonomy}.taxonomy = 'post_tag'
	AND intersected_term_taxonomy.term_id=%d
ORDER BY 
	{$wpdb->terms}.name ASC
SQL;
  $sql = $wpdb->prepare($sql,3); // 3 is your category ID

Hope this helps.

-Mike
[1] http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/

P.S. Too bad stuff like this isn't available natively in WP_Query.