Make WordPress Core


Ignore:
Timestamp:
10/15/2014 04:39:19 PM (9 years ago)
Author:
boonebgorges
Message:

Avoid redundant table joins in WP_Tax_Query.

IN clauses that are connected by OR require only a single table join. To avoid
extraneous joins, keep track of generated table aliases, and let sibling
clauses piggy-back on those aliases when possible.

Introduces WP_Tax_Query::sanitize_relation() to reduce some repeated code.

Adds unit tests to verify the JOIN consolidation, and integration tests for
cases where JOINS are being combined.

Props boonebgorges, otto42, jakub.tyrcha.
Fixes #18105.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/wp-includes/taxonomy.php

    r29896 r29902  
    716716     */
    717717    public function __construct( $tax_query ) {
    718         if ( isset( $tax_query['relation'] ) && strtoupper( $tax_query['relation'] ) == 'OR' ) {
    719             $this->relation = 'OR';
     718        if ( isset( $tax_query['relation'] ) ) {
     719            $this->relation = $this->sanitize_relation( $tax_query['relation'] );
    720720        } else {
    721721            $this->relation = 'AND';
     
    750750        foreach ( $queries as $key => $query ) {
    751751            if ( 'relation' === $key ) {
    752                 $cleaned_query['relation'] = $query;
     752                $cleaned_query['relation'] = $this->sanitize_relation( $query );
    753753
    754754            // First-order clause.
     
    787787
    788788                if ( ! empty( $cleaned_subquery ) ) {
     789                    // All queries with children must have a relation.
     790                    if ( ! isset( $cleaned_subquery['relation'] ) ) {
     791                        $cleaned_subquery['relation'] = 'AND';
     792                    }
     793
    789794                    $cleaned_query[] = $cleaned_subquery;
    790795                }
     
    793798
    794799        return $cleaned_query;
     800    }
     801
     802    /**
     803     * Sanitize a 'relation' operator.
     804     *
     805     * @since 4.1.0
     806     * @access public
     807     *
     808     * @param string $relation Raw relation key from the query argument.
     809     * @return Sanitized relation ('AND' or 'OR').
     810     */
     811    public function sanitize_relation( $relation ) {
     812        if ( 'OR' === strtoupper( $relation ) ) {
     813            return 'OR';
     814        } else {
     815            return 'AND';
     816        }
    795817    }
    796818
     
    853875     */
    854876    protected function get_sql_clauses() {
    855         $sql = $this->get_sql_for_query( $this->queries );
     877        /*
     878         * $queries are passed by reference to get_sql_for_query() for recursion.
     879         * To keep $this->queries unaltered, pass a copy.
     880         */
     881        $queries = $this->queries;
     882        $sql = $this->get_sql_for_query( $queries );
    856883
    857884        if ( ! empty( $sql['where'] ) ) {
     
    881908     * }
    882909     */
    883     protected function get_sql_for_query( $query, $depth = 0 ) {
     910    protected function get_sql_for_query( &$query, $depth = 0 ) {
    884911        $sql_chunks = array(
    885912            'join'  => array(),
     
    897924        }
    898925
    899         foreach ( $query as $key => $clause ) {
     926        foreach ( $query as $key => &$clause ) {
    900927            if ( 'relation' === $key ) {
    901928                $relation = $query['relation'];
     
    962989     * }
    963990     */
    964     public function get_sql_for_clause( $clause, $parent_query ) {
     991    public function get_sql_for_clause( &$clause, $parent_query ) {
    965992        global $wpdb;
    966993
     
    9891016            $terms = implode( ',', $terms );
    9901017
    991             $i = count( $this->table_aliases );
    992             $alias = $i ? 'tt' . $i : $wpdb->term_relationships;
    993             $this->table_aliases[] = $alias;
    994 
    995             $join .= " INNER JOIN $wpdb->term_relationships";
    996             $join .= $i ? " AS $alias" : '';
    997             $join .= " ON ($this->primary_table.$this->primary_id_column = $alias.object_id)";
     1018            /*
     1019             * Before creating another table join, see if this clause has a
     1020             * sibling with an existing join that can be shared.
     1021             */
     1022            $alias = $this->find_compatible_table_alias( $clause, $parent_query );
     1023            if ( false === $alias ) {
     1024                $i = count( $this->table_aliases );
     1025                $alias = $i ? 'tt' . $i : $wpdb->term_relationships;
     1026
     1027                // Store the alias as part of a flat array to build future iterators.
     1028                $this->table_aliases[] = $alias;
     1029
     1030                // Store the alias with this clause, so later siblings can use it.
     1031                $clause['alias'] = $alias;
     1032
     1033                $join .= " INNER JOIN $wpdb->term_relationships";
     1034                $join .= $i ? " AS $alias" : '';
     1035                $join .= " ON ($this->primary_table.$this->primary_id_column = $alias.object_id)";
     1036            }
     1037
    9981038
    9991039            $where = "$alias.term_taxonomy_id $operator ($terms)";
     
    10481088    }
    10491089
     1090    /**
     1091     * Identify an existing table alias that is compatible with the current query clause.
     1092     *
     1093     * We avoid unnecessary table joins by allowing each clause to look for
     1094     * an existing table alias that is compatible with the query that it
     1095     * needs to perform. An existing alias is compatible if (a) it is a
     1096     * sibling of $clause (ie, it's under the scope of the same relation),
     1097     * and (b) the combination of operator and relation between the clauses
     1098     * allows for a shared table join. In the case of WP_Tax_Query, this
     1099     * only applies to IN clauses that are connected by the relation OR.
     1100     *
     1101     * @since 4.1.0
     1102     * @access protected
     1103     *
     1104     * @param  array       $clause       Query clause.
     1105     * @param  array       $parent_query Parent query of $clause.
     1106     * @return string|bool Table alias if found, otherwise false.
     1107     */
     1108    protected function find_compatible_table_alias( $clause, $parent_query ) {
     1109        $alias = false;
     1110
     1111        // Sanity check. Only IN queries use the JOIN syntax .
     1112        if ( ! isset( $clause['operator'] ) || 'IN' !== $clause['operator'] ) {
     1113            return $alias;
     1114        }
     1115
     1116        // Since we're only checking IN queries, we're only concerned with OR relations.
     1117        if ( ! isset( $parent_query['relation'] ) || 'OR' !== $parent_query['relation'] ) {
     1118            return $alias;
     1119        }
     1120
     1121        $compatible_operators = array( 'IN' );
     1122
     1123        foreach ( $parent_query as $sibling ) {
     1124            if ( ! is_array( $sibling ) || ! $this->is_first_order_clause( $sibling ) ) {
     1125                continue;
     1126            }
     1127
     1128            if ( empty( $sibling['alias'] ) || empty( $sibling['operator'] ) ) {
     1129                continue;
     1130            }
     1131
     1132            // The sibling must both have compatible operator to share its alias.
     1133            if ( in_array( strtoupper( $sibling['operator'] ), $compatible_operators ) ) {
     1134                $alias = $sibling['alias'];
     1135                break;
     1136            }
     1137        }
     1138
     1139        return $alias;
     1140    }
    10501141
    10511142    /**
Note: See TracChangeset for help on using the changeset viewer.