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/tests/phpunit/tests/meta/query.php

    r29887 r29940  
    7474        $sql = $query->get_sql( 'post', $wpdb->posts, 'ID' );
    7575
    76         $this->assertEquals( 2, substr_count( $sql['join'], 'INNER JOIN' ) );
     76        $this->assertEquals( 1, substr_count( $sql['join'], 'INNER JOIN' ) );
    7777    }
    7878
     
    477477        $sql = $query->get_sql( 'post', $wpdb->posts, 'ID', $this );
    478478
    479         // We should have 2 joins - one for my_first_key and one for my_second_key
    480         $this->assertEquals( 2, substr_count( $sql['join'], 'JOIN' ) );
    481 
    482         // The WHERE should check my_third_key against an unaliased table
    483         $this->assertEquals( 1, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'my_third_key'" ) );
    484 
     479        $this->assertEquals( 3, substr_count( $sql['join'], 'JOIN' ) );
    485480    }
    486481
     
    547542        $this->assertSame( 0, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'baz'" ) );
    548543
    549         // When a value exists, it's not a key-only query
    550         $this->assertSame( 0, substr_count( $sql['where'], "$wpdb->postmeta.meta_key = 'barry'" ) );
    551 
    552544        // 'AND' queries don't have key-only queries
    553545        $query2 = new WP_Meta_Query( array(
Note: See TracChangeset for help on using the changeset viewer.