Optimize Multiple Taxonomy Term MySQL Query?

admin管理员组

文章数量:1426072

(Moderator's note: Title was originally: "query/database optimisation")

I'm written a function for a custom "filter" search panel that allows users to select terms from up to four custom taxonomies. I'm running the queries directly against the database and the query is averaging half a second to execute (with terms for all four taxonomies and one result returned).

This seems pretty slow to me. I was wondering if there's anything I can do to either optimise the query or even the database to make this more efficient/faster. Perhaps writing a view, even? I have experience with MS-SQL but not much with MySQL and I'm not sure how things are different.

Here's my function code:

    function filter_resources($phase,$wa,$aus,$topics){
    global $wpdb;
    $querystr="
    SELECT * 
        FROM $wpdb->posts A
            LEFT JOIN $wpdb->term_relationships B ON(A.ID = B.object_id)
            LEFT JOIN $wpdb->term_taxonomy C ON(B.term_taxonomy_id = C.term_taxonomy_id)
            LEFT JOIN $wpdb->terms D ON(C.term_id = D.term_id)

        LEFT JOIN $wpdb->term_relationships BB ON(A.ID = BB.object_id)
            LEFT JOIN $wpdb->term_taxonomy CC ON(BB.term_taxonomy_id = CC.term_taxonomy_id)
            LEFT JOIN $wpdb->terms DD ON(CC.term_id = DD.term_id)

        LEFT JOIN $wpdb->term_relationships BBB ON(A.ID = BBB.object_id)
            LEFT JOIN $wpdb->term_taxonomy CCC ON(BBB.term_taxonomy_id = CCC.term_taxonomy_id)
            LEFT JOIN $wpdb->terms DDD ON(CCC.term_id = DDD.term_id)

        LEFT JOIN $wpdb->term_relationships BBBB ON(A.ID = BBBB.object_id)
            LEFT JOIN $wpdb->term_taxonomy CCCC ON(BBBB.term_taxonomy_id = CCCC.term_taxonomy_id)
            LEFT JOIN $wpdb->terms DDDD ON(CCCC.term_id = DDDD.term_id)

        WHERE A.post_type = 'resources' 
            AND A.post_status = 'publish'
            AND C.taxonomy = 'phase-of-learning'
            AND D.term_id = '$phase'
            AND CC.taxonomy = 'wa-curriculum'
            AND DD.term_id = '$wa'
            AND CCC.taxonomy = 'australian-curriculum'
            AND DDD.term_id = '$aus'
            AND CCCC.taxonomy = 'topics'
            AND DDDD.term_id = '$topics'
        ORDER BY A.post_date DESC";
    return $wpdb->get_results($querystr,OBJECT);
}

Thanks!

(Moderator's note: Title was originally: "query/database optimisation")

I'm written a function for a custom "filter" search panel that allows users to select terms from up to four custom taxonomies. I'm running the queries directly against the database and the query is averaging half a second to execute (with terms for all four taxonomies and one result returned).

This seems pretty slow to me. I was wondering if there's anything I can do to either optimise the query or even the database to make this more efficient/faster. Perhaps writing a view, even? I have experience with MS-SQL but not much with MySQL and I'm not sure how things are different.

Here's my function code:

    function filter_resources($phase,$wa,$aus,$topics){
    global $wpdb;
    $querystr="
    SELECT * 
        FROM $wpdb->posts A
            LEFT JOIN $wpdb->term_relationships B ON(A.ID = B.object_id)
            LEFT JOIN $wpdb->term_taxonomy C ON(B.term_taxonomy_id = C.term_taxonomy_id)
            LEFT JOIN $wpdb->terms D ON(C.term_id = D.term_id)

        LEFT JOIN $wpdb->term_relationships BB ON(A.ID = BB.object_id)
            LEFT JOIN $wpdb->term_taxonomy CC ON(BB.term_taxonomy_id = CC.term_taxonomy_id)
            LEFT JOIN $wpdb->terms DD ON(CC.term_id = DD.term_id)

        LEFT JOIN $wpdb->term_relationships BBB ON(A.ID = BBB.object_id)
            LEFT JOIN $wpdb->term_taxonomy CCC ON(BBB.term_taxonomy_id = CCC.term_taxonomy_id)
            LEFT JOIN $wpdb->terms DDD ON(CCC.term_id = DDD.term_id)

        LEFT JOIN $wpdb->term_relationships BBBB ON(A.ID = BBBB.object_id)
            LEFT JOIN $wpdb->term_taxonomy CCCC ON(BBBB.term_taxonomy_id = CCCC.term_taxonomy_id)
            LEFT JOIN $wpdb->terms DDDD ON(CCCC.term_id = DDDD.term_id)

        WHERE A.post_type = 'resources' 
            AND A.post_status = 'publish'
            AND C.taxonomy = 'phase-of-learning'
            AND D.term_id = '$phase'
            AND CC.taxonomy = 'wa-curriculum'
            AND DD.term_id = '$wa'
            AND CCC.taxonomy = 'australian-curriculum'
            AND DDD.term_id = '$aus'
            AND CCCC.taxonomy = 'topics'
            AND DDDD.term_id = '$topics'
        ORDER BY A.post_date DESC";
    return $wpdb->get_results($querystr,OBJECT);
}

Thanks!

• mysql
• query
• optimization

Share Improve this question edited Feb 5, 2011 at 16:21 MikeSchinkel 37.6k1414 gold badges117117 silver badges132132 bronze badges asked Feb 4, 2011 at 4:54 goatladygoatlady 18511 silver badge77 bronze badges 2

• And before anyone points it out, I have modified the function to account for any combination of the four taxonomies being passed, but that version is longer and the extra conditionals don't add any additional clarity... :) – goatlady Commented Feb 4, 2011 at 5:14
• Can I assume you that your query only needs SELECT $wpdb->posts.* and not SELECT *? Also, you are using LEFT JOIN instead of INNER JOIN yet your WHERE clause makes your choice of `LEFT JOIN' moot. Can I confirm that was intentional? – MikeSchinkel Commented Feb 4, 2011 at 12:53

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 7

While this is really a MySQL question it does help to understand the WordPress SQL schema and also I love trying to optimize SQL queries so rather than send you off to StackOverflow I'll try to answer you here. You may still want to post it over there to get some other opinions.

And while I don't fully understand your requirement I think I understand what you are asking I think I do so I'd like to present the following to see if it meets your needs better. I don't have your data so it is a little hard for me to very that it indeed works but like I said, I think it meets your needs:

function filter_resources($phase,$wa,$aus,$topics){
  global $wpdb;
  $sql =<<<SQL
SELECT
  t.slug,p.*
FROM
  wp_posts p
  INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
  INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE 1=1
  AND p.post_type = 'resources'
  AND p.post_status = 'publish'
  AND t.term_id IN (%d,%d,%d,%d)
  AND CONCAT(tt.taxonomy,'/',t.term_id) IN (
    'phase-of-learning/%s',
    'wa-curriculum/%s',
    'australian-curriculum/%s',
    'topics/%s'
  )
GROUP BY
  p.ID
HAVING
  COUNT(*)=4
ORDER BY
  p.post_date DESC
SQL;
  $sql = $wpdb->prepare($sql,
    $phase,$wa,$aus,$topics,  // For the %d replacements
    $phase,$wa,$aus,$topics   // For the %s replacements
  );
  $results = $wpdb->get_results($sql,OBJECT);
  return $results;
}

Basically this gives you all posts where all of your taxonomy terms are applied and it does so by doing a freeform query to match all posts that have the taxonomy/terms applied but limits to only those posts that have all terms applied grouping by wp_post.ID and finding all records for which the post is joined 4 times. When you run a MySQL EXPLAIN the optimization looks pretty good compared to what you had; many fewer tables joined. Hopefully this was the logic you needed.

Caching with the Transients API

And if you are trying to improve performance you might also consider caching the results in a "transient" for a limited amount of time (1 hour, 4 hours, 12 hours or more?) This blog post describes how to use the WordPress Transients API:

• Overview of WordPress’ Transients API

Here's the basic logic for transients:

define('NUM_HOURS',4); // Time to cache set for your use case
$data = get_transient( 'your_transient_key' );
if( !$data ) {
  $data = // Do something to get your data here
  set_transient( 'your_transient_key', $data, 60 * 60 * NUM_HOURS );
}  

To use transients in your filter_resources() function it might instead look like this:

define('RESOURCE_CACHE_HOURS',4);
function filter_resources($phase,$wa,$aus,$topics){
  $resources = get_transient( 'yoursite_filtered_resources' );
  if(!$resources) {
    global $wpdb;
    $sql =<<<SQL
SELECT
  t.slug,p.*
FROM
  wp_posts p
  INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
  INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE 1=1
  AND p.post_type = 'resources'
  AND p.post_status = 'publish'
  AND t.term_id IN (%d,%d,%d,%d)
  AND CONCAT(tt.taxonomy,'/',t.term_id) IN (
    'phase-of-learning/%s',
    'wa-curriculum/%s',
    'australian-curriculum/%s',
    'topics/%s'
  )
GROUP BY
  p.ID
HAVING
  COUNT(*)=4
ORDER BY
  p.post_date DESC
SQL;
    $sql = $wpdb->prepare($sql,
      $phase,$wa,$aus,$topics,  // For the %d replacements
      $phase,$wa,$aus,$topics   // For the %s replacements
    );
    $resources = $wpdb->get_results($sql,OBJECT);
    $hours = RESOURCE_CACHE_HOURS * 60 * 60;
    set_transient( 'yoursite_filtered_resources', $resources, $hours);
  }  
  return $resources;
}

UPDATE

Here's another take on the code that is attempting to handle the cases where less than four criteria are selected by the user:

define('RESOURCE_CACHE_HOURS',4);
function filter_resources($phase,$wa,$aus,$topics){
  $resources = get_transient( 'yoursite_filtered_resources' );
  if(!$resources) {
    $terms = $taxterms = array();
    if (!empty($phase))
      $taxterms[$phase] = 'phase-of-learning/%s';
    if (!empty($wa)) 
      $taxterms[$wa] = 'wa-curriculum/%s';
    if (!empty($aus))
      $taxterms[$aus] = 'axustralian-curriculum/%s';
    if (!empty($topics))
      $taxterms[$topics] = 'topics/%s';
    $count = count($taxterms);
    $having = ($count==0 ? '' : "HAVING COUNT(*)={$count}");
    $values = array_keys(array_flip($tax_terms));
    $values = array_merge($values,$values);  // For %d and $s
    $taxterms =  implode("','",$taxterms);
    $terms = implode(',',array_fill(0,$count,'d%'));
    global $wpdb;
    $sql =<<<SQL
SELECT
  t.slug,p.*
FROM
  wp_posts p
  INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id
  INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
  INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE 1=1
  AND p.post_type = 'resources'
  AND p.post_status = 'publish'
  AND t.term_id IN ({$terms})
  AND CONCAT(tt.taxonomy,'/',t.term_id) IN ('{$taxterms}')
GROUP BY
  p.ID
{$having}
ORDER BY
  p.post_date DESC
SQL;
    $sql = $wpdb->prepare($sql,$values);
    $resources = $wpdb->get_results($sql,OBJECT);
    $hours = RESOURCE_CACHE_HOURS * 60 * 60;
    set_transient( 'yoursite_filtered_resources', $resources, $hours);
  }  
  return $resources;
}

Unless you need to stay backwards compatible with WP 3.0, you can just take advantage of the advanced taxonomy queries support in WP 3.1.

The code that generates the SQL can be found in wp-includes/taxonomy.php

First and foremost, use inner joins, not left joins. Left joins will force a query plan that scans the whole posts table until it find a matching post through your term filters.

Secondly, you can reduce the number of needed joins by pre-fetching the terms using get_term().

Combining the two, your query becomes something like:

SELECT * 
FROM $wpdb->posts posts
JOIN $wpdb->term_relationships termA
ON posts.ID = termA.object_id
AND termA.term_taxonomy_id = $termA_taxid
JOIN $wpdb->term_relationships termB
ON posts.ID = termB.object_id
AND termB.term_taxonomy_id = $termB_taxid
JOIN $wpdb->term_relationships termC
ON posts.ID = termC.object_id
AND termC.term_taxonomy_id = $termC_taxid
JOIN $wpdb->term_relationships termD
ON posts.ID = termD.object_id
AND termD.term_taxonomy_id = $termD_taxid
WHERE ...

Which should yield the same results with 5 joined tables instead of 13, and with a query plan that starts by looking up whichever posts tied to whichever term occurs the least frequently in term_relationships.

本文标签： Optimize Multiple Taxonomy Term MySQL Query