WP_Query: Mehrere Joins ersetzen
Veröffentlicht: – 2 Kommentare Letzte Aktualisierung:
Nachdem ich ein Filtersystem in eine Beitragssuche mit Beitrags-Metafeldern implementiert hatte, stieß ich auf ein großes Leistungsproblem. Je mehr Filter gesetzt wurden, desto langsamer wurde die Abfrage – exponentiell.
Das Problem
Ursprünglich habe ich die aktiven Filter über den pre_get_posts-Filter hinzugefügt. Für jeden aktiven Filter wurde so eine zusätzliche Meta-Abfrage zum WP_Query-Objekt hinzugefügt. So weit, so gewöhnlich. Und während es für einen Filter in angemessener Zeit funktionierte, dauerte das Hinzufügen eines zweiten Filters bereits mehrere Sekunden. Das Hinzufügen eines dritten Filters dauerte mehrere Minuten, und in einem lokalen Test führte das Hinzufügen eines vierten Filters auch nach mehreren Stunden nicht zu einem Ergebnis. Es gab etwas in der Abfrage, das für jedes Metafeld exponentiell länger dauerte. Falls du es nicht wusstest, WordPress führt für jedes Metafeld einen separaten INNER JOIN durch, der wie folgt aussieht:
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id )
INNER JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id )
WHERE 1=1
AND (
(
(
( wp_postmeta.meta_key = 'filter_1' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '5000' AND '10000' )
OR ( wp_postmeta.meta_key LIKE 'filter_1_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '5000' AND '10000' )
)
)
OR (
(
( mt1.meta_key = 'filter_2' AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '1980' AND '2016' )
OR ( mt1.meta_key LIKE 'filter_2_%_name' AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '1980' AND '2016' )
)
)
OR (
(
( mt2.meta_key = 'filter_3' AND CAST(mt2.meta_value AS DECIMAL) BETWEEN '49' AND '50' )
OR ( mt2.meta_key LIKE 'filter_3_%_name' AND CAST(mt2.meta_value AS DECIMAL) BETWEEN '49' AND '50' )
)
)
OR (
(
( mt3.meta_key = 'filter_4' AND CAST(mt3.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
OR ( mt3.meta_key LIKE 'filter_4_%_name' AND CAST(mt3.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
)
)
OR (
(
( mt4.meta_key = 'mast_hubhoehe_range' AND CAST(mt4.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
OR ( mt4.meta_key LIKE 'mast_hubhoehe_range_%_name' AND CAST(mt4.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
)
)
)
AND ((wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'product_homepage' OR wp_posts.post_status = 'private')))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10;Code-Sprache: SQL (Structured Query Language) (sql)Zuerst einmal schaute ich via EXPLAIN SELECT, was die Abfrage tat. Ohne ein wirkliches Problem zu erkennen:
+----+-------------+-------------+------------+-------+------------------------------------------------------------+------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+------------------------------------------------------------+------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | wp_posts | NULL | range | PRIMARY,post_name,type_status_date,post_parent,post_author | type_status_date | 164 | NULL | 6 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | wp_postmeta | NULL | ref | post_id,meta_key | post_id | 8 | wp_wd50_5.wp_posts.ID | 16 | 100.00 | NULL |
| 1 | SIMPLE | mt1 | NULL | ref | post_id,meta_key | post_id | 8 | wp_wd50_5.wp_posts.ID | 16 | 100.00 | NULL |
| 1 | SIMPLE | mt4 | NULL | ref | post_id,meta_key | post_id | 8 | wp_wd50_5.wp_posts.ID | 16 | 100.00 | NULL |
| 1 | SIMPLE | mt2 | NULL | ref | post_id,meta_key | post_id | 8 | wp_wd50_5.wp_posts.ID | 16 | 100.00 | NULL |
| 1 | SIMPLE | mt3 | NULL | ref | post_id,meta_key | post_id | 8 | wp_wd50_5.wp_posts.ID | 16 | 100.00 | Using where |
+----+-------------+-------------+------------+-------+------------------------------------------------------------+------------------+---------+-----------------------+------+----------+-----------------------------------------------------------+
6 rows in set, 2 warnings (0.01 sec)
Code-Sprache: PHP (php)Zu einem einzelnen JOIN umschreiben
Meine erste Idee war, einen einzelnen INNER JOIN für alle Metafelder zu verwenden. Das schien auch zu funktionieren, wenn alle Beitrags-Metafelder via OR behandelt wurden. Für AND musste es dabei bleiben, dass jedes Metafeld ein einzelnes JOIN behielt. Glücklicherweise (in diesem Fall), war die Ausführungszeit der Abfrage nur so lang, wenn die Metafelder via OR abgefragt wurden.
Daher verwendete ich den Filter posts_clauses, um die Abfrage entsprechend anzupassen. Dieser Filter erlaubt dir, die eigentliche SQL-Abfrage vor ihrer Ausführung zu manipulieren. Der Inhalt des Filters sieht dabei in etwa so aus:
array(7) {
["where"]=>
string(1993) " AND (
(
(
( wp_postmeta.meta_key = 'filter_1' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '5000' AND '10000' )
OR ( wp_postmeta.meta_key LIKE 'filter_1_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '5000' AND '10000' )
)
)
OR (
(
( mt1.meta_key = 'filter_2' AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '1980' AND '2016' )
OR ( mt1.meta_key LIKE 'filter_2_%_name' AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '1980' AND '2016' )
)
)
OR (
(
( mt2.meta_key = 'filter_3' AND CAST(mt2.meta_value AS DECIMAL) BETWEEN '49' AND '50' )
OR ( mt2.meta_key LIKE 'filter_3_%_name' AND CAST(mt2.meta_value AS DECIMAL) BETWEEN '49' AND '50' )
)
)
OR (
(
( mt3.meta_key = 'filter_4' AND CAST(mt3.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
OR ( mt3.meta_key LIKE 'filter_4_%_name' AND CAST(mt3.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
)
)
OR (
(
( mt4.meta_key = 'mast_hubhoehe_range' AND CAST(mt4.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
OR ( mt4.meta_key LIKE 'mast_hubhoehe_range_%_name' AND CAST(mt4.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
)
)
) AND ((wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'internal_area' OR wp_posts.post_status = 'product_homepage'
OR wp_posts.post_status = 'private')))"
["groupby"]=>
string(13) "wp_posts.ID"
["join"]=>
string(206) " INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )"
["orderby"]=>
string(25) "wp_posts.post_date DESC"
["distinct"]=>
string(0) ""
["fields"]=>
string(12) "wp_posts.*"
["limits"]=>
string(11) "LIMIT 0, 10"
}
Code-Sprache: SQL (Structured Query Language) (sql)Was ich hier tun musste, war das Entfernen aller nachfolgenden Joins. Dafür musste ich zuerst alle mtX-Tabellennamen im where-Schlüssel mit jenem der eigentlichen Beitrags-Metatabelle ersetzen:
$clauses['where'] = (string) \preg_replace( '/mt\d+\.meta_/', $query->meta_query->meta_table . '.meta_', $clauses['where'] );Code-Sprache: PHP (php)Hierdurch wird mt1 zu wp_postmeta, ebenso wie mt2, mt3 und mt4.
Dann muss ich im join-Schlüssel prüfen, wo das erste Mal mt1 vorkommt und es entfernen, da wir die nachfolgenden Joins nicht mehr benötigen.
$second_inner_join = ' INNER JOIN ' . $query->meta_query->meta_table . ' AS mt1';
$second_inner_join_position = \strpos( $clauses['join'], $second_inner_join );
if ( $second_inner_join_position !== false ) {
$clauses['join'] = \substr( $clauses['join'], 0, $second_inner_join_position );
}
Code-Sprache: PHP (php)Der Wert des Filter sieht nun in etwa so aus:
array(7) {
["where"]=>
string(1993) " AND (
(
(
( wp_postmeta.meta_key = 'filter_1' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '5000' AND '10000' )
OR ( wp_postmeta.meta_key LIKE 'filter_1_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '5000' AND '10000' )
)
)
OR (
(
( wp_postmeta.meta_key = 'filter_2' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '1980' AND '2016' )
OR ( wp_postmeta.meta_key LIKE 'filter_2_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '1980' AND '2016' )
)
)
OR (
(
( wp_postmeta.meta_key = 'filter_3' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '49' AND '50' )
OR ( wp_postmeta.meta_key LIKE 'filter_3_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '49' AND '50' )
)
)
OR (
(
( wp_postmeta.meta_key = 'filter_4' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
OR ( wp_postmeta.meta_key LIKE 'filter_4_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
)
)
OR (
(
( wp_postmeta.meta_key = 'mast_hubhoehe_range' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
OR ( wp_postmeta.meta_key LIKE 'mast_hubhoehe_range_%_name' AND CAST(wp_postmeta.meta_value AS DECIMAL) BETWEEN '500' AND '1000' )
)
)
) AND ((wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'internal_area' OR wp_posts.post_status = 'product_homepage'
OR wp_posts.post_status = 'private')))"
["groupby"]=>
string(13) "wp_posts.ID"
["join"]=>
string(206) " INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )"
["orderby"]=>
string(25) "wp_posts.post_date DESC"
["distinct"]=>
string(0) ""
["fields"]=>
string(12) "wp_posts.*"
["limits"]=>
string(11) "LIMIT 0, 10"
}
Code-Sprache: SQL (Structured Query Language) (sql)Wie dir möglicherweise bereits aufgefallen ist, verwende ich diesen Filter in einem individuellen Inhaltstyp product. Deshalb prüfe ich in meinem Hook-Callback zierst, ob ich in der Haupt-Abfrage bin und diese zu meinem Inhaltstyp gehört:
if (
(
! $query->is_main_query()
|| $query->query_vars['post_type'] !== 'product'
|| ! $query->is_archive()
)
&& ! \has_action( 'my_plugin_enforce_filter' )
) {
return $clauses;
}
Code-Sprache: PHP (php)Zusätzlich prüfe ich ebenso, ob eine spezifische Action über add_action ausgeführt wurde, sodass ich die Manipulation der Abfrage optional auch umgehen kann.
AND-Beziehung
Wie oben erwähnt, sind weiterhin mehrere Joins bei einer AND-Beziehung der Metafelder erforderlich. Deshalb füge ich diese erneut hinzu, wenn diese Option aktiviert ist (in meinem Fall über eine globale Einstellung):
if ( \get_option( 'my_plugin_filter_relation', 'OR' ) === 'AND' ) {
/** @var \wpdb $wpdb */
global $wpdb;
$join_count = 0;
foreach ( $query->meta_query as $key_query ) {
if ( ! \is_array( $key_query ) ) {
continue;
}
foreach ( $key_query as $meta_query ) {
if ( ! \is_array( $meta_query ) ) {
continue;
}
++$join_count;
$clauses['join'] .= \sprintf(
' INNER JOIN %1$s AS mt%2$d ON (%3$s.ID = mt%2$d.post_id AND (mt%2$d.meta_key = \'%4$s\' OR mt%2$d.meta_key LIKE \'%5$s\') AND %6$s %7$s %8$s) ',
$wpdb->postmeta,
$join_count,
$wpdb->posts,
$meta_query[0]['key'],
$meta_query[0]['key'] . '_%_name',
'mt' . $join_count . '.meta_value',
$meta_query[0]['compare'],
"'" . \implode( '\' AND \'', $meta_query[0]['value'] ) . "'"
) . \PHP_EOL;
}
}
}
Code-Sprache: PHP (php)Warum die Joins dann in diesem Fall überhaupt zuerst entfernen und sie dann wieder selbst hinzufügen? Für eine bessere Wartbarkeit, da ich so sicherstellen kann, dass die Basis identisch und die Abfrage in beiden Fällen gleich aufgebaut ist.
Fazit
Bis heute weiß ich nicht, was genau passiert, das solche Abfragen exponentiell langsamer werden lässt. Die Lösung oben sehe ich eher als Workaround, aber als funktionierend. Wenn du einen Tipp hast, wieso das passiert, kommentiere gern. 🙂
Moin!
Vielleicht ein Thema für das Performance Team?
Es grüßt
derRALF
Guter Punkt!