admin管理员组

文章数量:1023116

I need to process some meta_query fields.

$meta_query = array(
                'relation' => 'OR',
                [
                    'key'       => '_sale_price',
                    'value'     => $price_range,
                    'compare'   => 'BETWEEN',
                    'type'      => 'NUMERIC'
                ],
                [
                    'relation' => 'AND',
                    [
                        'key'     => '_regular_price',
                        'value'   => $price_range,
                        'compare' => 'BETWEEN',
                        'type'    => 'NUMERIC',
                    ],
                    [
                        'key'     => '_sale_price',
                        'value'   => '1',
                        'compare' => '<',
                    ]
                ]
            );
$query->set('meta_query',$meta_query);

This produces an SQL query with many INNER JOINS and it is very slow!

Full query is:

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 )
LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup
ON wp_posts.ID = wc_product_meta_lookup.product_id
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (10,423) ) )
AND ( ( wp_postmeta.meta_key = '_sale_price'
AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '100'
AND '199' )
OR ( ( mt1.meta_key = '_regular_price'
AND CAST(mt1.meta_value AS SIGNED) BETWEEN '100'
AND '199' )
AND ( mt2.meta_key = '_sale_price'
AND mt2.meta_value < '1' ) ) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wc_product_meta_lookup.max_price DESC, wc_product_meta_lookup.product_id DESC
LIMIT 0, 24

Same result can be achieved using simple WHERE statements and avoid expensive joins. How can I get Wordpress to generate more efficient SQL in this case?

Thanks, Rudolf

I need to process some meta_query fields.

$meta_query = array(
                'relation' => 'OR',
                [
                    'key'       => '_sale_price',
                    'value'     => $price_range,
                    'compare'   => 'BETWEEN',
                    'type'      => 'NUMERIC'
                ],
                [
                    'relation' => 'AND',
                    [
                        'key'     => '_regular_price',
                        'value'   => $price_range,
                        'compare' => 'BETWEEN',
                        'type'    => 'NUMERIC',
                    ],
                    [
                        'key'     => '_sale_price',
                        'value'   => '1',
                        'compare' => '<',
                    ]
                ]
            );
$query->set('meta_query',$meta_query);

This produces an SQL query with many INNER JOINS and it is very slow!

Full query is:

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 )
LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup
ON wp_posts.ID = wc_product_meta_lookup.product_id
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (10,423) ) )
AND ( ( wp_postmeta.meta_key = '_sale_price'
AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '100'
AND '199' )
OR ( ( mt1.meta_key = '_regular_price'
AND CAST(mt1.meta_value AS SIGNED) BETWEEN '100'
AND '199' )
AND ( mt2.meta_key = '_sale_price'
AND mt2.meta_value < '1' ) ) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wc_product_meta_lookup.max_price DESC, wc_product_meta_lookup.product_id DESC
LIMIT 0, 24

Same result can be achieved using simple WHERE statements and avoid expensive joins. How can I get Wordpress to generate more efficient SQL in this case?

Thanks, Rudolf

本文标签： Very slow query generated getting meta data from posts

版权声明：本文标题：Very slow query generated getting meta data from posts 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745546387a2155411.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。