WordPress查询+/-300k数据库条目和7个内部联接时速度非常慢

情况是这样的，我们有一个WP DB，它有+/-300k个帖子，并且有一个复杂的查询，必须检查不同的元和交叉不同的信息，等等。下面是查询：

SELECT SQL_CALC_FOUND_ROWS sr_posts.ID FROM sr_posts
LEFT JOIN sr_term_relationships ON (sr_posts.ID = sr_term_relationships.object_id)
INNER JOIN sr_postmeta ON ( sr_posts.ID = sr_postmeta.post_id )
INNER JOIN sr_postmeta AS mt1 ON ( sr_posts.ID = mt1.post_id )
INNER JOIN sr_postmeta AS mt2 ON ( sr_posts.ID = mt2.post_id )
INNER JOIN sr_postmeta AS mt3 ON ( sr_posts.ID = mt3.post_id )
INNER JOIN sr_postmeta AS mt4 ON ( sr_posts.ID = mt4.post_id )
INNER JOIN sr_postmeta AS mt5 ON ( sr_posts.ID = mt5.post_id )
INNER JOIN sr_postmeta AS mt6 ON ( sr_posts.ID = mt6.post_id )
WHERE 1=1 AND ( sr_term_relationships.term_taxonomy_id IN (1293,1294,1295) ) AND ( ( sr_postmeta.meta_key = \'_start_price\' AND CAST(sr_postmeta.meta_value AS SIGNED) BETWEEN \'0\' AND \'99999\' ) AND ( ( mt1.meta_key = \'event_upto_date\' AND CAST(mt1.meta_value AS SIGNED) <= \'20170723\' ) OR ( ( mt2.meta_key = \'event_end_date_comp\' AND CAST(mt2.meta_value AS SIGNED) <= \'20170723\' ) AND ( mt3.meta_key = \'event_end_date_comp\' AND mt3.meta_value != \'\' ) ) ) AND ( ( ( mt4.meta_key = \'event_upto_date\' AND CAST(mt4.meta_value AS SIGNED) >= \'20170722\' ) OR ( mt4.meta_key = \'event_end_date_comp\' AND CAST(mt4.meta_value AS SIGNED) >= \'20170722\' ) ) ) AND ( mt5.meta_key = \'is_single_day_event\' AND mt6.meta_key = \'event_upto_date\' ) ) AND sr_posts.post_type = \'event\' AND ((sr_posts.post_status = \'publish\'))
GROUP BY sr_posts.ID ORDER BY CAST(mt6.meta_value AS CHAR) ASC, CAST(mt5.meta_value AS CHAR) DESC LIMIT 0, 18

我们首先尝试优化代码，即WP创建此查询的方式（我们使用的是WP\\U查询，而不是自定义查询）。没有成功！

然后，我们尝试进一步调查，并在phpMyAdmin上使用“概要文件”，从总共花费的65.8516秒中可以看出，65秒是在“将结果存储在查询缓存中”。

然后，我们搜索并发现了两种可能的结果：

增加query\\u缓存的可用内存（我们从80M提高到256M）完全停用query\\u缓存

所以我在这里，向那边的船长寻求帮助！：）

提前非常感谢。

编辑1：

只需删除以下内容，我就可以将查询时间减少到17秒：

GROUP BY sr_posts.ID ORDER BY CAST(mt6.meta_value AS CHAR) ASC, CAST(mt5.meta_value AS CHAR) DESC LIMIT 0, 18

问题是。。。我显然需要这个！：P

编辑2：

CAST可能是罪魁祸首，但在这种情况下不是，因为我移除了，它什么也没做。。。将加载结果保持在63到67秒之间。

编辑3（我自己问题的答案）：

简而言之，问题在于我不是在内部联接上立即“过滤”，而是在WHERE上进行过滤，这导致查询速度慢得多。下面是正确的问题：

SELECT
 SQL_CALC_FOUND_ROWS sr_posts.ID
FROM sr_posts
 LEFT JOIN sr_term_relationships
  ON (sr_posts.ID = sr_term_relationships.object_id)
 INNER JOIN sr_postmeta
  ON ( sr_posts.ID = sr_postmeta.post_id )
 INNER JOIN sr_postmeta AS mt1
  ON ( sr_posts.ID = mt1.post_id ) AND mt1.meta_key = \'event_upto_date\'
 INNER JOIN sr_postmeta AS mt2
  ON ( sr_posts.ID = mt2.post_id ) AND mt2.meta_key = \'event_end_date_comp\'
 INNER JOIN sr_postmeta AS mt3
  ON ( sr_posts.ID = mt3.post_id ) AND mt3.meta_key = \'event_end_date_comp\'
 INNER JOIN sr_postmeta AS mt4
  ON ( sr_posts.ID = mt4.post_id )
 INNER JOIN sr_postmeta AS mt5
  ON ( sr_posts.ID = mt5.post_id ) AND mt5.meta_key = \'is_single_day_event\'
 INNER JOIN sr_postmeta AS mt6
  ON ( sr_posts.ID = mt6.post_id ) AND mt6.meta_key = \'event_upto_date\'
WHERE
 sr_term_relationships.term_taxonomy_id IN (1293,1294,1295) AND
 ( ( sr_postmeta.meta_key = \'_start_price\' AND CAST(sr_postmeta.meta_value AS SIGNED) BETWEEN \'0\' AND \'99999\' ) AND ( ( CAST(mt1.meta_value AS SIGNED) <= \'20170723\' ) OR ( ( CAST(mt2.meta_value AS SIGNED) <= \'20170723\' ) AND ( mt3.meta_value != \'\' ) ) ) AND ( ( ( mt4.meta_key = \'event_upto_date\' AND CAST(mt4.meta_value AS SIGNED) >= \'20170722\' ) OR ( mt4.meta_key = \'event_end_date_comp\' AND CAST(mt4.meta_value AS SIGNED) >= \'20170722\' ) ) ) ) AND
 sr_posts.post_type = \'event\' AND
 sr_posts.post_status = \'publish\'
GROUP BY
 sr_posts.ID
ORDER BY
 CAST(mt6.meta_value AS CHAR) ASC,
 CAST(mt5.meta_value AS CHAR) DESC
LIMIT 0, 18

此查询需要+/-1秒。。。与67秒相比，这是一个“小区别”：D

小码农CODE

相关推荐

Pre_Get_Posts-编辑查询，Tax_Query