WordPress.org

Make WordPress Core


Ignore:
Timestamp:
10/17/2014 08:20:04 PM (5 years ago)
Author:
boonebgorges
Message:

Overhaul SQL generating logic in WP_Meta_Query to avoid unnecessary table joins.

The logic used to generate clause SQL in WP_Meta_Query is somewhat arcane,
stemming mostly from an ongoing effort to eliminate costly table joins when
they are not necessary. By systematizing the process of looking for shareable
joins - as was done in WP_Tax_Query [29902] - it becomes possible to simplify
the construction of SQL queries in get_sql_for_clause(). Moreover, the
simplified logic is actually considerably better at identifying shareable
joins, such that certain uses of WP_Meta_Query will see joins reduced by 50%
or more.

Includes integration tests for a representative cross-section of the query
clause combinations that result in shared table aliases.

Props boonebgorges, sc0ttkclark.
See #24093.

File:
1 edited

Legend:

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

    r29890 r29940  
    988988            // First-order clause.
    989989            } else if ( $this->is_first_order_clause( $query ) ) {
     990                if ( isset( $query['value'] ) && array() === $query['value'] ) {
     991                    unset( $query['value'] );
     992                }
     993
    990994                $clean_queries[] = $query;
    991995
     
    11681172     */
    11691173    protected function get_sql_clauses() {
    1170         $sql = $this->get_sql_for_query( $this->queries );
     1174        /*
     1175         * $queries are passed by reference to get_sql_for_query() for recursion.
     1176         * To keep $this->queries unaltered, pass a copy.
     1177         */
     1178        $queries = $this->queries;
     1179        $sql = $this->get_sql_for_query( $queries );
    11711180
    11721181        if ( ! empty( $sql['where'] ) ) {
     
    11961205     * }
    11971206     */
    1198     protected function get_sql_for_query( $query, $depth = 0 ) {
     1207    protected function get_sql_for_query( &$query, $depth = 0 ) {
    11991208        $sql_chunks = array(
    12001209            'join'  => array(),
     
    12121221        }
    12131222
    1214         foreach ( $query as $key => $clause ) {
     1223        foreach ( $query as $key => &$clause ) {
    12151224            if ( 'relation' === $key ) {
    12161225                $relation = $query['relation'];
     
    12791288     * }
    12801289     */
    1281     public function get_sql_for_clause( $clause, $parent_query ) {
     1290    public function get_sql_for_clause( &$clause, $parent_query ) {
    12821291        global $wpdb;
    12831292
     
    12871296        );
    12881297
    1289         $i = count( $this->table_aliases );
    1290         $alias = $i ? 'mt' . $i : $this->meta_table;
    1291 
    12921298        if ( isset( $clause['compare'] ) ) {
    1293             $meta_compare = strtoupper( $clause['compare'] );
     1299            $clause['compare'] = strtoupper( $clause['compare'] );
    12941300        } else {
    1295             $meta_compare = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '=';
    1296         }
    1297 
    1298         if ( ! in_array( $meta_compare, array(
     1301            $clause['compare'] = isset( $clause['value'] ) && is_array( $clause['value'] ) ? 'IN' : '=';
     1302        }
     1303
     1304        if ( ! in_array( $clause['compare'], array(
    12991305            '=', '!=', '>', '>=', '<', '<=',
    13001306            'LIKE', 'NOT LIKE',
     
    13041310            'REGEXP', 'NOT REGEXP', 'RLIKE'
    13051311        ) ) ) {
    1306             $meta_compare = '=';
    1307         }
    1308 
    1309         /*
    1310          * There are a number of different query structures that get
    1311          * built in different ways.
    1312          * 1. Key-only clauses - (a) clauses without a 'value' key that
    1313          *    appear in the context of an OR relation and do not use
    1314          *    'NOT EXISTS' as the 'compare', or (b) clauses with an
    1315          *    empty array for 'value'.
    1316          */
    1317         if ( ! empty( $clause['key'] ) && (
    1318             ( ! array_key_exists( 'value', $clause ) && 'NOT EXISTS' !== $meta_compare && 'OR' === $parent_query['relation'] ) ||
    1319             ( isset( $clause['value'] ) && is_array( $clause['value'] ) && empty( $clause['value'] ) )
    1320         ) ) {
    1321 
    1322             $alias = $this->meta_table;
    1323             $sql_chunks['join'][] = " INNER JOIN $this->meta_table ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column)";
    1324             $sql_chunks['where'][] = $wpdb->prepare( "$this->meta_table.meta_key = %s", trim( $clause['key'] ) );
    1325 
    1326         // 2. NOT EXISTS.
    1327         } else if ( 'NOT EXISTS' === $meta_compare ) {
    1328             $join  = " LEFT JOIN $this->meta_table";
    1329             $join .= $i ? " AS $alias" : '';
    1330             $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key'] );
     1312            $clause['compare'] = '=';
     1313        }
     1314
     1315        $meta_compare = $clause['compare'];
     1316
     1317        // First build the JOIN clause, if one is required.
     1318        $join = '';
     1319
     1320        // We prefer to avoid joins if possible. Look for an existing join compatible with this clause.
     1321        $alias = $this->find_compatible_table_alias( $clause, $parent_query );
     1322        if ( false === $alias ) {
     1323            $i = count( $this->table_aliases );
     1324            $alias = $i ? 'mt' . $i : $this->meta_table;
     1325
     1326            // JOIN clauses for NOT EXISTS have their own syntax.
     1327            if ( 'NOT EXISTS' === $meta_compare ) {
     1328                $join .= " LEFT JOIN $this->meta_table";
     1329                $join .= $i ? " AS $alias" : '';
     1330                $join .= $wpdb->prepare( " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key'] );
     1331                $sql_chunks['join'][] = $join;
     1332
     1333            // All other JOIN clauses.
     1334            } else {
     1335                $alias = $this->find_compatible_table_alias( $clause, $parent_query );
     1336                if ( false === $alias ) {
     1337                    $alias = $i ? 'mt' . $i : $this->meta_table;
     1338
     1339                    $join .= " INNER JOIN $this->meta_table";
     1340                    $join .= $i ? " AS $alias" : '';
     1341                    $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column )";
     1342                }
     1343            }
     1344
     1345            $this->table_aliases[] = $alias;
    13311346            $sql_chunks['join'][] = $join;
    1332 
    1333             $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL';
    1334 
    1335         // 3. EXISTS and other key-only queries.
    1336         } else if ( 'EXISTS' === $meta_compare || ( ! empty( $clause['key'] ) && ! array_key_exists( 'value', $clause ) ) ) {
    1337             $join  = " INNER JOIN $this->meta_table";
    1338             $join .= $i ? " AS $alias" : '';
    1339             $join .= " ON ( $this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column )";
    1340             $sql_chunks['join'][] = $join;
    1341 
    1342             $sql_chunks['where'][] = $wpdb->prepare( $alias . '.meta_key = %s', trim( $clause['key'] ) );
    1343 
    1344         // 4. Clauses that have a value.
    1345         } else if ( array_key_exists( 'value', $clause ) ) {
    1346             $join  = " INNER JOIN $this->meta_table";
    1347             $join .= $i ? " AS $alias" : '';
    1348             $join .= " ON ($this->primary_table.$this->primary_id_column = $alias.$this->meta_id_column)";
    1349             $sql_chunks['join'][] = $join;
    1350 
    1351             if ( ! empty( $clause['key'] ) ) {
     1347        }
     1348
     1349        // Save the alias to this clause, for future siblings to find.
     1350        $clause['alias'] = $alias;
     1351
     1352        // Next, build the WHERE clause.
     1353        $where = '';
     1354
     1355        // meta_key.
     1356        if ( array_key_exists( 'key', $clause ) ) {
     1357            if ( 'NOT EXISTS' === $meta_compare ) {
     1358                $sql_chunks['where'][] = $alias . '.' . $this->meta_id_column . ' IS NULL';
     1359            } else {
    13521360                $sql_chunks['where'][] = $wpdb->prepare( "$alias.meta_key = %s", trim( $clause['key'] ) );
    13531361            }
    1354 
     1362        }
     1363
     1364        // meta_value.
     1365        if ( array_key_exists( 'value', $clause ) ) {
     1366            $meta_value = $clause['value'];
    13551367            $meta_type = $this->get_cast_for_type( isset( $clause['type'] ) ? $clause['type'] : '' );
    1356 
    1357             $meta_value = isset( $clause['value'] ) ? $clause['value'] : '';
    13581368
    13591369            if ( in_array( $meta_compare, array( 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN' ) ) ) {
     
    13651375            }
    13661376
    1367             if ( 'IN' == substr( $meta_compare, -2 ) ) {
    1368                 $meta_compare_string = '(' . substr( str_repeat( ',%s', count( $meta_value ) ), 1 ) . ')';
    1369             } elseif ( 'BETWEEN' == substr( $meta_compare, -7 ) ) {
    1370                 $meta_value = array_slice( $meta_value, 0, 2 );
    1371                 $meta_compare_string = '%s AND %s';
    1372             } elseif ( 'LIKE' == $meta_compare || 'NOT LIKE' == $meta_compare ) {
    1373                 $meta_value = '%' . $wpdb->esc_like( $meta_value ) . '%';
    1374                 $meta_compare_string = '%s';
    1375             } else {
    1376                 $meta_compare_string = '%s';
     1377            switch ( $meta_compare ) {
     1378                case 'IN' :
     1379                case 'NOT IN' :
     1380                    $meta_compare_string = '(' . substr( str_repeat( ',%s', count( $meta_value ) ), 1 ) . ')';
     1381                    $where = $wpdb->prepare( $meta_compare_string, $meta_value );
     1382                    break;
     1383
     1384                case 'BETWEEN' :
     1385                case 'NOT BETWEEN' :
     1386                    $meta_value = array_slice( $meta_value, 0, 2 );
     1387                    $where = $wpdb->prepare( '%s AND %s', $meta_value );
     1388                    break;
     1389
     1390                case 'LIKE' :
     1391                case 'NOT LIKE' :
     1392                    $meta_value = '%' . $wpdb->esc_like( $meta_value ) . '%';
     1393                    $where = $wpdb->prepare( '%s', $meta_value );
     1394                    break;
     1395
     1396                default :
     1397                    $where = $wpdb->prepare( '%s', $meta_value );
     1398                    break;
     1399
    13771400            }
    13781401
    1379             $sql_chunks['where'][] = $wpdb->prepare( "CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$meta_compare_string}", $meta_value );
     1402            if ( $where ) {
     1403                $sql_chunks['where'][] = "CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$where}";
     1404            }
    13801405        }
    13811406
     
    13881413        }
    13891414
    1390         $this->table_aliases[] = $alias;
    1391 
    13921415        return $sql_chunks;
     1416    }
     1417
     1418    /**
     1419     * Identify an existing table alias that is compatible with the current query clause.
     1420     *
     1421     * We avoid unnecessary table joins by allowing each clause to look for
     1422     * an existing table alias that is compatible with the query that it
     1423     * needs to perform. An existing alias is compatible if (a) it is a
     1424     * sibling of $clause (ie, it's under the scope of the same relation),
     1425     * and (b) the combination of operator and relation between the clauses
     1426     * allows for a shared table join. In the case of WP_Meta_Query, this
     1427     * only applies to IN clauses that are connected by the relation OR.
     1428     *
     1429     * @since 4.1.0
     1430     * @access protected
     1431     *
     1432     * @param  array       $clause       Query clause.
     1433     * @param  array       $parent_query Parent query of $clause.
     1434     * @return string|bool Table alias if found, otherwise false.
     1435     */
     1436    protected function find_compatible_table_alias( $clause, $parent_query ) {
     1437        $alias = false;
     1438
     1439        foreach ( $parent_query as $sibling ) {
     1440            // If the sibling has no alias yet, there's nothing to check.
     1441            if ( empty( $sibling['alias'] ) ) {
     1442                continue;
     1443            }
     1444
     1445            // We're only interested in siblings that are first-order clauses.
     1446            if ( ! is_array( $sibling ) || ! $this->is_first_order_clause( $sibling ) ) {
     1447                continue;
     1448            }
     1449
     1450            $compatible_compares = array();
     1451
     1452            // Clauses connected by OR can share joins as long as they have "positive" operators.
     1453            if ( 'OR' === $parent_query['relation'] ) {
     1454                $compatible_compares = array( '=', 'IN', 'BETWEEN', 'LIKE', 'REGEXP', 'RLIKE', '>', '>=', '<', '<=' );
     1455
     1456            // Clauses joined by AND with "negative" operators share a join only if they also share a key.
     1457            } else if ( isset( $sibling['key'] ) && isset( $clause['key'] ) && $sibling['key'] === $clause['key'] ) {
     1458                $compatible_compares = array( '!=', 'NOT IN', 'NOT LIKE' );
     1459            }
     1460
     1461            $clause_compare  = strtoupper( $clause['compare'] );
     1462            $sibling_compare = strtoupper( $sibling['compare'] );
     1463            if ( in_array( $clause_compare, $compatible_compares ) && in_array( $sibling_compare, $compatible_compares ) ) {
     1464                $alias = $sibling['alias'];
     1465                break;
     1466            }
     1467        }
     1468
     1469        return apply_filters( 'meta_query_find_compatible_table_alias', $alias, $clause, $parent_query, $this ) ;
    13931470    }
    13941471}
Note: See TracChangeset for help on using the changeset viewer.