php - 有没有办法为同一个表组合多个连接？用于 V 4.6 的 WP 元查询

Question

我卡在后元过滤器中，它在结束查询执行超时时花费了太多时间来检索结果......

我不是在寻找增加时间或任何我需要为我的查询组合多个连接或任何其他解决方案

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 ) 
INNER JOIN wp_postmeta AS mt5 ON ( wp_posts.id = mt5.post_id ) 
INNER JOIN wp_postmeta AS mt6 ON ( wp_posts.id = mt6.post_id ) 
INNER JOIN wp_postmeta AS mt7 ON ( wp_posts.id = mt7.post_id ) 
INNER JOIN wp_postmeta AS mt8 ON ( wp_posts.id = mt8.post_id ) 
WHERE 1=1
AND (
    wp_postmeta.meta_key = 'wa_prod_Company' 
    AND (
        ( mt1.meta_key = 'wa_prod_Duration' AND mt1.meta_value LIKE '%%' ) 
        AND ( mt2.meta_key = 'wa_prod_Connection' AND mt2.meta_value > '0' ) 
        AND ( mt3.meta_key = 'wa_prod_BW' AND mt3.meta_value >= '0' ) 
        AND ( mt4.meta_key = 'wa_prod_HDD' AND Cast(mt4.meta_value AS SIGNED) >= '0' ) 
        AND ( mt5.meta_key = 'wa_prod_Price' AND Cast(mt5.meta_value AS SIGNED) BETWEEN '0' AND '2000' )
        AND ( mt6.meta_key = 'wa_prod_Availability' AND mt6.meta_value LIKE '%on%' ) 
        AND (
                ( mt7.meta_key = 'wa_prod_Type' AND mt7.meta_value LIKE '%Shared%' ) 
            OR  ( mt7.meta_key = 'wa_prod_Type' AND mt7.meta_value LIKE '%Semi Dedicated%' )
            OR  ( mt7.meta_key = 'wa_prod_Type' AND mt7.meta_value LIKE '%Dedicated%' ) 
            ) 
        AND (
                ( mt8.meta_key = 'wa_prod_Premium_Featurs' AND mt8.meta_value LIKE '%SSD%' )
            ) 
        ) 
    ) 
AND wp_posts.post_type = 'product' 
AND (( wp_posts.post_status = 'publish' )) 
GROUP BY wp_posts.id 
ORDER BY wp_postmeta.meta_value ASC limit 0, 10

我尝试只加入一个 INNER JOIN wp_postmeta ON ( wp_posts.id = wp_postmeta.post_id ) 并删除其他和 mt1，mt2 替换为 wp_postmeta 但它没有检索结果。

我还有几个过滤器，但在应用 8 个过滤器查询之后，由于 SQL Server 超时，没有检索到任何东西......

我也试过meta-query-terribly-slow但不起作用，因为它不支持 WP V 4.6

Answer 1

1)此查询的临时解决方案增加了服务器执行时间对于最大执行时间尝试:

设置时间限制(0);

同时尝试优化您的查询。并在 50 或 100 个时隙的每个间隔之间使用 sleep 。

2) 尽可能少地使用 like，尽可能使用 mt7.meta_key = 'wa_prod_Type' 和 mt7.meta_value = 'Shared'。