mysql - 如何使这些 wp_postmeta MySQL 查询更快？

Question

我正在制作一个搜索功能，当在几个下拉菜单中的每一个上进行选择时，该功能都会更新可用选项。

这里有两个 mysql 查询，运行它们来更新两个下拉菜单。数据库中约有 5000 个产品，每次查询大约需要 4 秒。我怎样才能加快它们的速度？

我尝试将它们组合成一个查询并使用 PHP 进行分组，但是如果清除了某个选项，则所选下拉列表中的选项将不可用。

SELECT DISTINCT l.meta_value AS ship_id
FROM wp_posts a
JOIN wp_postmeta f ON ID = f.post_id
JOIN wp_postmeta g ON ID = g.post_id
JOIN wp_postmeta h ON ID = h.post_id
JOIN wp_postmeta d ON ID = d.post_id JOIN wp_postmeta i ON ID = i.post_id 
JOIN wp_postmeta l ON ID = l.post_id
WHERE post_type = 'product'
AND f.meta_key = 'first_start_date'
AND g.meta_key = 'last_start_date'
AND h.meta_key = 'product_group'
AND h.meta_value = 'cruises'
AND d.meta_key = 'destinationregion'
AND i.meta_key = 'destinationarea'
AND 'South America' IN (d.meta_value, i.meta_value)
AND (
        (CONCAT('2016', '01-01') BETWEEN STR_TO_DATE(f.meta_value, '%b %e, %Y') AND STR_TO_DATE(g.meta_value, '%b %e, %Y')) OR
        (STR_TO_DATE(f.meta_value, '%b %e, %Y') BETWEEN CONCAT('2016', '-01-01') AND CONCAT('2016' + 1, '-01-01'))
    )
AND l.meta_key = 'cruiseship'
ORDER BY l.meta_value ASC

-

SELECT DISTINCT d.meta_value AS destinationregion, i.meta_value AS destinationarea
FROM wp_posts a
JOIN wp_postmeta f ON ID = f.post_id
JOIN wp_postmeta g ON ID = g.post_id
JOIN wp_postmeta h ON ID = h.post_id
JOIN wp_postmeta d ON ID = d.post_id JOIN wp_postmeta i ON ID = i.post_id
WHERE post_type = 'product'
AND f.meta_key = 'first_start_date'
AND g.meta_key = 'last_start_date'
AND h.meta_key = 'product_group'
AND h.meta_value = 'cruises'
AND (
        (CONCAT('2016', '01-01') BETWEEN STR_TO_DATE(f.meta_value, '%b %e, %Y') AND STR_TO_DATE(g.meta_value, '%b %e, %Y')) OR
        (STR_TO_DATE(f.meta_value, '%b %e, %Y') BETWEEN CONCAT('2016', '-01-01') AND CONCAT('2016' + 1, '-01-01'))
    )
AND d.meta_key = 'destinationregion' AND i.meta_key = 'destinationarea'
ORDER BY destinationregion, destinationarea"

Answer 1

您可以尝试查询的聚合形式:

想法是:

select p.id,
       max(case when pm.meta_key = 'First_start_date' then pm.meta_value end) as first_start_date,
       max(case when pm.meta_key = 'last_start_date' then pm.meta_value end) as last_start_date,       
       max(case when pm.meta_key = 'product_group' then pm.meta_value end) as product_group,
       max(case when pm.meta_key = 'destinationregion' then pm.meta_value end) as destinationregion,
       max(case when pm.meta_key = 'destinationarea' then pm.meta_value end) as destinationarea,
       max(case when pm.meta_key = 'cruiseship' then pm.meta_value end) as cruiseship
from wp_posts p join
     wp_postmeta pm
     on pm.post_id = p.id
where p.post_type = 'product'
group by p.id ;

您可以使用 having 子句或子查询来对值进行特定过滤条件。

wp_posts(product, id) 上的索引可能有助于查询。