Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

(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!

share|improve this question
    
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 Feb 4 '11 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 Feb 4 '11 at 12:53
add comment

3 Answers 3

up vote 6 down vote accepted

Hi @goatlady:

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:

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;
}
share|improve this answer
    
+1 for query optimisation compared to the original question. –  Zack Feb 4 '11 at 16:55
    
@Zack - Thanks. I hope it matches her needs. I couldn't really test it enough without building up relevant test data. –  MikeSchinkel Feb 4 '11 at 17:02
    
First, wow - thanks! I know it's hard without data :( This returns results in my db tool in .391 seconds which is a bit faster and definitely more efficient... however I'm not sure how I could modify it to return results if only one, two or three of the terms were searched on. This is a screenshot of the "filter" box: [link]smoljak.com/filters.jpg - so you can see that if one of the taxonomies is left blank, it's not factored into the query. in my version I used php conditionals in the function to decide whether to include each section of SQL or not. –  goatlady Feb 5 '11 at 2:13
    
Also will look into the transients stuff... looks like it might be a good addition. –  goatlady Feb 5 '11 at 2:14
    
@goatlady - I added an update to address less than four terms searched; let me know if it works for you. –  MikeSchinkel Feb 5 '11 at 3:26
show 3 more comments

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

share|improve this answer
    
I hadn't heard about this - obviously the WP team are realising the need for easier querying now that they've added all the tasty custom taxonomy stuff. It does say at the bottom of that article however "Advanced taxonomy queries are cool, but be aware that complex queries are going to be slower" which makes me think I'd be better off with a custom SQL solution for this one. –  goatlady Feb 5 '11 at 2:19
    
Please also read the comments on that post. Bottom line is that you're unlikely to come up with a faster solution. –  scribu Feb 5 '11 at 9:13
    
The built in advanced taxonomy queries solution is actually going to be gobs faster than anything else you can come up with. The joins its doing are optimized for speed, as it's just joining indexed integer tables together. It does do some pre-selecting first to get the values it needs to build those joins, but unlike your original solution, it's only doing one join per taxonomy instead of three for the final query. –  Otto Feb 5 '11 at 9:26
1  
@Otto - "Gobs faster than anything else"; Bit of hyperbole, no? –  MikeSchinkel Feb 5 '11 at 16:28
    
@Otto - Just for the sidenote: I'm missing querying all posts in a taxonomy regardless of term somehow. Need to prefetch all IDs first to work around that. Additionally it's not possible to combine AND and OR and have grouping for those. –  hakre Feb 6 '11 at 14:59
add comment

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.

share|improve this answer
    
This is basically what Advanced Taxonomy Queries in 3.1 does, albeit in a more configurable and expandable fashion. –  Otto Feb 5 '11 at 9:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.