[wp-trac] [WordPress Trac] #49278: Improve meta query
WordPress Trac
noreply at wordpress.org
Thu Jan 23 16:32:32 UTC 2020
#49278: Improve meta query
-------------------------+-----------------------------
Reporter: jillebehm | Owner: (none)
Type: enhancement | Status: new
Priority: normal | Milestone: Awaiting Review
Component: Query | Version: 5.3.2
Severity: normal | Keywords: dev-feedback
Focuses: performance |
-------------------------+-----------------------------
When having a couple of Meta Query statements in WP_Query the query
becomes very slow. I think this is because of the way the JOINs are
created.
Currently the JOINs are only done on the Post ID. The JOIN can become
enormous, which means that filtering (the WHERE part) will take a lot of
time.
I checked /wp-includes/class-wp-meta-query.php and posted the code between
line 557 and 573 .
{{{
// JOIN clauses for NOT EXISTS have their own syntax.
if ( 'NOT EXISTS' === $meta_compare ) {
$join .= " LEFT JOIN $this->meta_table";
$join .= $i ? " AS $alias" : '';
if ( 'LIKE' === $meta_compare_key ) {
$join .= $wpdb->prepare( " ON
($this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column AND $alias.meta_key LIKE %s )", '%' .
$wpdb->esc_like( $clause['key'] ) . '%' );
} else {
$join .= $wpdb->prepare( " ON
($this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key']
);
}
// All other JOIN clauses.
} else {
$join .= " INNER JOIN $this->meta_table";
$join .= $i ? " AS $alias" : '';
$join .= " ON (
$this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column )";
}
}}}
Apparantly when using the 'NOT EXISTS' compare the 'AND $alias.meta_key'
part is added to the JOIN, but when NOT using the 'NOT EXISTS' compare
this part is not there.
This means that when NOT using the 'NOT EXISTS' compare the a lot of data
is joined in the temporary data set. I played with this part a bit and
when adding the 'AND $alias.meta_key' part to those JOINs as well it sped
up my query a lot. My query went from 38 seconds to 0.01 seconds with the
same results.
My 'test' code:
{{{
// JOIN clauses for NOT EXISTS have their own syntax.
if ( 'NOT EXISTS' === $meta_compare ) {
$join .= " LEFT JOIN $this->meta_table";
$join .= $i ? " AS $alias" : '';
if ( 'LIKE' === $meta_compare_key ) {
$join .= $wpdb->prepare( " ON
($this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column AND $alias.meta_key LIKE %s )", '%' .
$wpdb->esc_like( $clause['key'] ) . '%' );
} else {
$join .= $wpdb->prepare( " ON
($this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key']
);
}
// All other JOIN clauses.
} else {
$join .= " INNER JOIN $this->meta_table";
$join .= $i ? " AS $alias" : '';
$valid_compares = array(
'=',
'!=',
'>',
'>=',
'<',
'<=',
'IN',
'NOT IN',
'EXISTS',
);
if( in_array($meta_compare,
$valid_compares ) && !empty($clause['key']) && 'LIKE' !==
$meta_compare_key ) {
$join .= $wpdb->prepare( " ON (
$this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column AND $alias.meta_key = %s )", $clause['key']);
}
else {
$join .= " ON (
$this->primary_table.$this->primary_id_column =
$alias.$this->meta_id_column )";
}
}
}}}
I'm not really sure if this works in all cases (with all
compare/compare_key variations), but I think it would be good to check it
out (on Github I've seen that the last improvements here have been done at
least 2 years ago).
For now I 'solved' my slow query by parsing the JOIN and WHERE on the
filter 'get_meta_sql' and add the 'AND' part in the JOIN.
Below the query that gets created before and after the changes.
Query before (38 seconds):
{{{
SELECT SQL_CALC_FOUND_ROWS riff19_posts.ID FROM riff19_posts INNER JOIN
riff19_postmeta ON ( riff19_posts.ID = riff19_postmeta.post_id ) INNER
JOIN riff19_postmeta AS mt1 ON ( riff19_posts.ID = mt1.post_id ) INNER
JOIN riff19_postmeta AS mt2 ON ( riff19_posts.ID = mt2.post_id ) INNER
JOIN riff19_postmeta AS mt3 ON ( riff19_posts.ID = mt3.post_id ) JOIN
riff19_icl_translations wpml_translations
ON riff19_posts.ID
= wpml_translations.element_id
AND
wpml_translations.element_type = CONCAT('post_', riff19_posts.post_type)
WHERE 1=1 AND (
( riff19_postmeta.meta_key = 'pinplugin_event_start_date' AND
CAST(riff19_postmeta.meta_value AS DATE) < '2020-01-23' )
OR
(
(
( mt1.meta_key = 'pinplugin_event_start_date' AND
CAST(mt1.meta_value AS DATE) = '2020-01-23' )
AND
mt2.meta_key = 'pinplugin_event_start_time'
AND
( mt3.meta_key = 'pinplugin_event_end_time' AND CAST(mt3.meta_value
AS TIME) <= '17:19:19' )
)
)
) AND riff19_posts.post_type = 'event' AND (riff19_posts.post_status =
'publish' OR riff19_posts.post_status = 'acf-disabled' OR
riff19_posts.post_status = 'private') AND ( ( (
wpml_translations.language_code = 'nl' OR 0 ) AND riff19_posts.post_type
IN
('post','page','attachment','wp_block','location','person','news','blog','case','service','event','vacancy','whitepaper'
) ) OR riff19_posts.post_type NOT IN
('post','page','attachment','wp_block','location','person','news','blog','case','service','event','vacancy','whitepaper'
) ) GROUP BY riff19_posts.ID ORDER BY riff19_posts.menu_order,
CAST(riff19_postmeta.meta_value AS DATE) DESC, CAST(mt2.meta_value AS
TIME) DESC, CAST(mt3.meta_value AS TIME) DESC LIMIT 0, 12
}}}
Query after (0.0028 seconds):
{{{
SELECT SQL_CALC_FOUND_ROWS riff19_posts.ID FROM riff19_posts INNER JOIN
riff19_postmeta ON ( riff19_posts.ID = riff19_postmeta.post_id AND
riff19_postmeta.meta_key = 'pinplugin_event_start_date') INNER JOIN
riff19_postmeta AS mt1 ON ( riff19_posts.ID = mt1.post_id AND mt1.meta_key
= 'pinplugin_event_start_date') INNER JOIN riff19_postmeta AS mt2 ON (
riff19_posts.ID = mt2.post_id AND mt2.meta_key =
'pinplugin_event_start_time') INNER JOIN riff19_postmeta AS mt3 ON (
riff19_posts.ID = mt3.post_id AND mt3.meta_key =
'pinplugin_event_end_time') JOIN riff19_icl_translations wpml_translations
ON riff19_posts.ID
= wpml_translations.element_id
AND
wpml_translations.element_type = CONCAT('post_', riff19_posts.post_type)
WHERE 1=1 AND (
( riff19_postmeta.meta_key = 'pinplugin_event_start_date' AND
CAST(riff19_postmeta.meta_value AS DATE) < '2020-01-23' )
OR
(
(
( mt1.meta_key = 'pinplugin_event_start_date' AND
CAST(mt1.meta_value AS DATE) = '2020-01-23' )
AND
mt2.meta_key = 'pinplugin_event_start_time'
AND
( mt3.meta_key = 'pinplugin_event_end_time' AND CAST(mt3.meta_value
AS TIME) <= '17:18:05' )
)
)
) AND riff19_posts.post_type = 'event' AND (riff19_posts.post_status =
'publish' OR riff19_posts.post_status = 'acf-disabled' OR
riff19_posts.post_status = 'private') AND ( ( (
wpml_translations.language_code = 'nl' OR 0 ) AND riff19_posts.post_type
IN
('post','page','attachment','wp_block','location','person','news','blog','case','service','event','vacancy','whitepaper'
) ) OR riff19_posts.post_type NOT IN
('post','page','attachment','wp_block','location','person','news','blog','case','service','event','vacancy','whitepaper'
) ) GROUP BY riff19_posts.ID ORDER BY riff19_posts.menu_order,
CAST(riff19_postmeta.meta_value AS DATE) DESC, CAST(mt2.meta_value AS
TIME) DESC, CAST(mt3.meta_value AS TIME) DESC LIMIT 0, 12
}}}
--
Ticket URL: <https://core.trac.wordpress.org/ticket/49278>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform
More information about the wp-trac
mailing list