Changeset 6253

Timestamp:

10/15/2007 08:14:56 PM (17 years ago)

Author:

ryan

Message:

Use inner joins instead of left joins for taxonomy. Inner is faster. fixes #5179 for 2.3

Location:

branches/2.3/wp-includes

Files:

• bookmark.php (modified) (1 diff)
• query.php (modified) (5 diffs)
• taxonomy.php (modified) (1 diff)

branches/2.3/wp-includes/bookmark.php

@@ -114 +114 @@
     if (!empty($category_query)) {
         $category_query .= ") AND taxonomy = 'link_category'";
-        $join = " LEFT JOIN $wpdb->term_relationships AS tr ON ($wpdb->links.link_id = tr.object_id) LEFT JOIN $wpdb->term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id";
+        $join = " INNER JOIN $wpdb->term_relationships AS tr ON ($wpdb->links.link_id = tr.object_id) INNER JOIN $wpdb->term_taxonomy as tt ON tt.term_taxonomy_id = tr.term_taxonomy_id";
     }
 

branches/2.3/wp-includes/query.php

@@ -933 +933 @@
 
         if ( !empty($q['category__in']) ) {
-            $join = " LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
+            $join = " INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
             $whichcat .= " AND $wpdb->term_taxonomy.taxonomy = 'category' ";
             $include_cats = "'" . implode("', '", $q['category__in']) . "'";
@@ -974 +974 @@
             $q['cat'] = $reqcat;
 
-            $join = " LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
+            $join = " INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
             $whichcat = " AND $wpdb->term_taxonomy.taxonomy = 'category' ";
             $in_cats = array($q['cat']);
@@ -1016 +1016 @@
 
         if ( !empty($q['tag__in']) ) {
-            $join = " LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
+            $join = " INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
             $whichcat .= " AND $wpdb->term_taxonomy.taxonomy = 'post_tag' ";
             $include_tags = "'" . implode("', '", $q['tag__in']) . "'";
@@ -1026 +1026 @@
 
         if ( !empty($q['tag_slug__in']) ) {
-            $join = " LEFT JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) LEFT JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id) ";
+            $join = " INNER JOIN $wpdb->term_relationships ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id) INNER JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) INNER JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id) ";
             $whichcat .= " AND $wpdb->term_taxonomy.taxonomy = 'post_tag' ";
             $include_tags = "'" . implode("', '", $q['tag_slug__in']) . "'";
@@ -1057 +1057 @@
 
             $q[$item] = array_unique($q[$item]);
-            $tsql = "SELECT p.ID FROM $wpdb->posts p LEFT JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id) LEFT JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) LEFT JOIN $wpdb->terms t ON (tt.term_id = t.term_id)";
+            $tsql = "SELECT p.ID FROM $wpdb->posts p INNER JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id) INNER JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id) INNER JOIN $wpdb->terms t ON (tt.term_id = t.term_id)";
             $tsql .= " WHERE tt.taxonomy = '$taxonomy' AND t.$taxonomy_field IN ('" . implode("', '", $q[$item]) . "')";
             $tsql .= " GROUP BY p.ID HAVING count(p.ID) = " . count($q[$item]);

branches/2.3/wp-includes/taxonomy.php

@@ -1358 +1358 @@
 
     // Get the object and term ids and stick them in a lookup table
-    $results = $wpdb->get_results("SELECT object_id, term_taxonomy_id FROM $wpdb->term_relationships LEFT JOIN $wpdb->posts ON object_id = ID WHERE term_taxonomy_id IN (".join(',', array_keys($term_ids)).") AND post_type = 'post' AND post_status = 'publish'");
+    $results = $wpdb->get_results("SELECT object_id, term_taxonomy_id FROM $wpdb->term_relationships INNER JOIN $wpdb->posts ON object_id = ID WHERE term_taxonomy_id IN (".join(',', array_keys($term_ids)).") AND post_type = 'post' AND post_status = 'publish'");
     foreach ( $results as $row ) {
         $id = $term_ids[$row->term_taxonomy_id];