Woocommerce slow query with joins
Ik loop tegen iets aan en dat is het volgende.
In wc-term-functions.php van WP Woocommerce staat een query zoals hieronder. Deze query vertraagd de boel enorm en doet er gemiddeld 1 a 2 seconden over een resultaat te geven. probleem is dat deze continu door het systeem wordt aangeroepen dus de boel loopt vol met locks. Er zijn bijna 40.000 producten en meer dan 1.4 miljoen records in wp_postmeta. De server die ik heb is sterk zat en heeft 4x CPU met 16gb dus dat wil wel.
De query
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// Main query
$count_query = "
SELECT COUNT( DISTINCT posts.ID ) FROM {$wpdb->posts} as posts
LEFT JOIN {$wpdb->postmeta} AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id
$stock_join
WHERE post_status = 'publish'
AND post_type = 'product'
AND meta_visibility.meta_key = '_visibility'
AND meta_visibility.meta_value IN ( 'visible', 'catalog' )
$stock_query
";
$count_query = "
SELECT COUNT( DISTINCT posts.ID ) FROM {$wpdb->posts} as posts
LEFT JOIN {$wpdb->postmeta} AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id
$stock_join
WHERE post_status = 'publish'
AND post_type = 'product'
AND meta_visibility.meta_key = '_visibility'
AND meta_visibility.meta_value IN ( 'visible', 'catalog' )
$stock_query
";
De volgende url komt er bijvoorbeeld uit:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT COUNT( DISTINCT posts.ID ) FROM wp_posts as posts
LEFT JOIN wp_postmeta AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN wp_term_relationships AS rel ON posts.ID=rel.object_ID
LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
LEFT JOIN wp_terms AS term USING( term_id )
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
WHERE post_status = 'publish' AND post_type = 'product'
AND meta_visibility.meta_key = '_visibility' AND meta_visibility.meta_value IN ( 'visible', 'catalog' )
AND term_id IN ( 2735 );
LEFT JOIN wp_postmeta AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN wp_term_relationships AS rel ON posts.ID=rel.object_ID
LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
LEFT JOIN wp_terms AS term USING( term_id )
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
WHERE post_status = 'publish' AND post_type = 'product'
AND meta_visibility.meta_key = '_visibility' AND meta_visibility.meta_value IN ( 'visible', 'catalog' )
AND term_id IN ( 2735 );
Explain:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
tax
ref
PRIMARY,term_id_taxonomy
term_id_taxonomy
8
const
1
Using index
1
SIMPLE
rel
ref
PRIMARY,term_taxonomy_id
term_taxonomy_id
8
ietsdoen_wp.tax.term_taxonomy_id
60
Using index
1
SIMPLE
posts
eq_ref
PRIMARY,wp_posts_idx1,type_status_date
PRIMARY
8
ietsdoen_wp.rel.object_id
1
Using where
1
SIMPLE
meta_visibility
ref
post_id,meta_key,wp_postmeta_idx1,wp_postmeta_idx2
post_id
8
ietsdoen_wp.rel.object_id
7
Using where
1
SIMPLE
postmeta
ref
post_id
post_id
8
ietsdoen_wp.rel.object_id
7
Using index
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
tax
ref
PRIMARY,term_id_taxonomy
term_id_taxonomy
8
const
1
Using index
1
SIMPLE
rel
ref
PRIMARY,term_taxonomy_id
term_taxonomy_id
8
ietsdoen_wp.tax.term_taxonomy_id
60
Using index
1
SIMPLE
posts
eq_ref
PRIMARY,wp_posts_idx1,type_status_date
PRIMARY
8
ietsdoen_wp.rel.object_id
1
Using where
1
SIMPLE
meta_visibility
ref
post_id,meta_key,wp_postmeta_idx1,wp_postmeta_idx2
post_id
8
ietsdoen_wp.rel.object_id
7
Using where
1
SIMPLE
postmeta
ref
post_id
post_id
8
ietsdoen_wp.rel.object_id
7
Using index
Heeft iemand een tip om het misschien anders te doen waardoor het sneller kan. Ik las iets over een group by toevoegen maar snap ik niet helemaal.
gr, Rik
1) een cache ervoor gooien
2) Nog meer geheugen en mysql tweaken dat hij dat allemaal ook gebruikt.
3) je AND meta_visibility.meta_key = '_visibility' AND meta_visibility.meta_value IN ( 'visible', 'catalog' ) in je JOIN zetten. Dan filtert hij het er al namelijk eerder uit.
4) Naar de volgorde van je joins kijken.
5) Waarom zijn het left joins? Wellicht dat er 1 of 2 ook inner joins kunnen zijn waardoor de dataset kleiner wordt
Algemeen is voor mysql dat hoe sneller je de dataset kan verkleinen hoe sneller de query is.
Remco van Bers op 27/03/2017 17:12:52:
Paar dingen die je kan doen:
1) een cache ervoor gooien
2) Nog meer geheugen en mysql tweaken dat hij dat allemaal ook gebruikt.
3) je AND meta_visibility.meta_key = '_visibility' AND meta_visibility.meta_value IN ( 'visible', 'catalog' ) in je JOIN zetten. Dan filtert hij het er al namelijk eerder uit.
4) Naar de volgorde van je joins kijken.
5) Waarom zijn het left joins? Wellicht dat er 1 of 2 ook inner joins kunnen zijn waardoor de dataset kleiner wordt
Algemeen is voor mysql dat hoe sneller je de dataset kan verkleinen hoe sneller de query is.
1) een cache ervoor gooien
2) Nog meer geheugen en mysql tweaken dat hij dat allemaal ook gebruikt.
3) je AND meta_visibility.meta_key = '_visibility' AND meta_visibility.meta_value IN ( 'visible', 'catalog' ) in je JOIN zetten. Dan filtert hij het er al namelijk eerder uit.
4) Naar de volgorde van je joins kijken.
5) Waarom zijn het left joins? Wellicht dat er 1 of 2 ook inner joins kunnen zijn waardoor de dataset kleiner wordt
Algemeen is voor mysql dat hoe sneller je de dataset kan verkleinen hoe sneller de query is.
bedankt voor je antwoord.
1 & 2 zijn we druk mee.
3 tot met 5 vind ik wat lastig want dit is gewoon iets uit woocommerce en het is me niet helemaal duidelijk waarom ze deze volgorde hebben.
Dit zit allemaal in dezelfde functie:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
function _wc_term_recount( $terms, $taxonomy, $callback = true, $terms_are_term_taxonomy_ids = true ) {
global $wpdb;
// Standard callback
if ( $callback ) {
_update_post_term_count( $terms, $taxonomy );
}
// Stock query
if ( get_option( 'woocommerce_hide_out_of_stock_items' ) == 'yes' ) {
$stock_join = "LEFT JOIN {$wpdb->postmeta} AS meta_stock ON posts.ID = meta_stock.post_id";
$stock_query = "
AND meta_stock.meta_key = '_stock_status'
AND meta_stock.meta_value = 'instock'
";
} else {
$stock_query = $stock_join = '';
}
// Main query
$count_query = "
SELECT COUNT( DISTINCT posts.ID ) FROM {$wpdb->posts} as posts
LEFT JOIN {$wpdb->postmeta} AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id
$stock_join
WHERE post_status = 'publish'
AND post_type = 'product'
AND meta_visibility.meta_key = '_visibility'
AND meta_visibility.meta_value IN ( 'visible', 'catalog' )
$stock_query
";
// Pre-process term taxonomy ids
if ( ! $terms_are_term_taxonomy_ids ) {
// We passed in an array of TERMS in format id=>parent
$terms = array_filter( (array) array_keys( $terms ) );
} else {
// If we have term taxonomy IDs we need to get the term ID
$term_taxonomy_ids = $terms;
$terms = array();
foreach ( $term_taxonomy_ids as $term_taxonomy_id ) {
$term = get_term_by( 'term_taxonomy_id', $term_taxonomy_id, $taxonomy->name );
$terms[] = $term->term_id;
}
}
// Exit if we have no terms to count
if ( empty( $terms ) ) {
return;
}
// Ancestors need counting
if ( is_taxonomy_hierarchical( $taxonomy->name ) ) {
foreach ( $terms as $term_id ) {
$terms = array_merge( $terms, get_ancestors( $term_id, $taxonomy->name ) );
}
}
// Unique terms only
$terms = array_unique( $terms );
// Count the terms
foreach ( $terms as $term_id ) {
$terms_to_count = array( absint( $term_id ) );
if ( is_taxonomy_hierarchical( $taxonomy->name ) ) {
// We need to get the $term's hierarchy so we can count its children too
if ( ( $children = get_term_children( $term_id, $taxonomy->name ) ) && ! is_wp_error( $children ) ) {
$terms_to_count = array_unique( array_map( 'absint', array_merge( $terms_to_count, $children ) ) );
}
}
// Generate term query
$term_query = 'AND term_id IN ( ' . implode( ',', $terms_to_count ) . ' )';
// Get the count
$count = $wpdb->get_var( $count_query . $term_query );
// Update the count
update_woocommerce_term_meta( $term_id, 'product_count_' . $taxonomy->name, absint( $count ) );
}
delete_transient( 'wc_term_counts' );
}
global $wpdb;
// Standard callback
if ( $callback ) {
_update_post_term_count( $terms, $taxonomy );
}
// Stock query
if ( get_option( 'woocommerce_hide_out_of_stock_items' ) == 'yes' ) {
$stock_join = "LEFT JOIN {$wpdb->postmeta} AS meta_stock ON posts.ID = meta_stock.post_id";
$stock_query = "
AND meta_stock.meta_key = '_stock_status'
AND meta_stock.meta_value = 'instock'
";
} else {
$stock_query = $stock_join = '';
}
// Main query
$count_query = "
SELECT COUNT( DISTINCT posts.ID ) FROM {$wpdb->posts} as posts
LEFT JOIN {$wpdb->postmeta} AS meta_visibility ON posts.ID = meta_visibility.post_id
LEFT JOIN {$wpdb->term_relationships} AS rel ON posts.ID=rel.object_ID
LEFT JOIN {$wpdb->term_taxonomy} AS tax USING( term_taxonomy_id )
LEFT JOIN {$wpdb->terms} AS term USING( term_id )
LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id
$stock_join
WHERE post_status = 'publish'
AND post_type = 'product'
AND meta_visibility.meta_key = '_visibility'
AND meta_visibility.meta_value IN ( 'visible', 'catalog' )
$stock_query
";
// Pre-process term taxonomy ids
if ( ! $terms_are_term_taxonomy_ids ) {
// We passed in an array of TERMS in format id=>parent
$terms = array_filter( (array) array_keys( $terms ) );
} else {
// If we have term taxonomy IDs we need to get the term ID
$term_taxonomy_ids = $terms;
$terms = array();
foreach ( $term_taxonomy_ids as $term_taxonomy_id ) {
$term = get_term_by( 'term_taxonomy_id', $term_taxonomy_id, $taxonomy->name );
$terms[] = $term->term_id;
}
}
// Exit if we have no terms to count
if ( empty( $terms ) ) {
return;
}
// Ancestors need counting
if ( is_taxonomy_hierarchical( $taxonomy->name ) ) {
foreach ( $terms as $term_id ) {
$terms = array_merge( $terms, get_ancestors( $term_id, $taxonomy->name ) );
}
}
// Unique terms only
$terms = array_unique( $terms );
// Count the terms
foreach ( $terms as $term_id ) {
$terms_to_count = array( absint( $term_id ) );
if ( is_taxonomy_hierarchical( $taxonomy->name ) ) {
// We need to get the $term's hierarchy so we can count its children too
if ( ( $children = get_term_children( $term_id, $taxonomy->name ) ) && ! is_wp_error( $children ) ) {
$terms_to_count = array_unique( array_map( 'absint', array_merge( $terms_to_count, $children ) ) );
}
}
// Generate term query
$term_query = 'AND term_id IN ( ' . implode( ',', $terms_to_count ) . ' )';
// Get the count
$count = $wpdb->get_var( $count_query . $term_query );
// Update the count
update_woocommerce_term_meta( $term_id, 'product_count_' . $taxonomy->name, absint( $count ) );
}
delete_transient( 'wc_term_counts' );
}
Misschien heb je een voorbeeld geven van hoe jij het zou opbouwen ?
Wellicht iemand hier die wel ervaring heeft met woocommerce?
Remco van Bers op 28/03/2017 10:09:18:
Heb zelf geen ervaring met woocommerce en kijkend naar dat deze functie veel te veel doet ga ik hier ook niks mee kunnen zonder het product te onderzoeken.
Wellicht iemand hier die wel ervaring heeft met woocommerce?
Wellicht iemand hier die wel ervaring heeft met woocommerce?
Daar zit ik zelf ook een beetje mee.