如何使这些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)上建立索引可能会对查询有所帮助。

Answer 2

修改您的数据模型，以便在不需要任何其他功能的情况下运行以下内容：

WHERE f.meta_value < '2017-01-01' AND g.meta_value > '2016-01-01'

我不记得您是否可以自定义wp，以便根据数据类型在表中排列值，但如果不能，那么至少将日期存储为格式正确的字符串。

我假设在(post_id，meta_key，meta_value)上有一个复合索引