在wordpress上长时间运行的查询使网站关闭。不知道这个问题的原因[已结束]

Question

 SELECT 
    COUNT(*) 
FROM wp_posts 
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) 
LEFT JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id) 
LEFT JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id) 
LEFT JOIN wp_term_relationships AS tt4 ON (wp_posts.ID = tt4.object_id) 
LEFT JOIN wp_term_relationships AS tt5 ON (wp_posts.ID = tt5.object_id) 
LEFT JOIN wp_term_relationships AS tt6 ON (wp_posts.ID = tt6.object_id) 
LEFT JOIN wp_term_relationships AS tt7 ON (wp_posts.ID = tt7.object_id) 
LEFT JOIN wp_term_relationships AS tt8 ON (wp_posts.ID = tt8.object_id) 
LEFT JOIN wp_term_relationships AS tt9 ON (wp_posts.ID = tt9.object_id) 
LEFT JOIN wp_term_relationships AS tt10 ON (wp_posts.ID = tt10.object_id) 
LEFT JOIN wp_term_relationships AS tt11 ON (wp_posts.ID = tt11.object_id) 
LEFT JOIN wp_term_relationships AS tt12 ON (wp_posts.ID = tt12.object_id) 
LEFT JOIN wp_term_relationships AS tt13 ON (wp_posts.ID = tt13.object_id) 
LEFT JOIN wp_term_relationships AS tt14 ON (wp_posts.ID = tt14.object_id) 
LEFT JOIN wp_term_relationships AS tt15 ON (wp_posts.ID = tt15.object_id) 
LEFT JOIN wp_term_relationships AS tt16 ON (wp_posts.ID = tt16.object_id) 
LEFT JOIN wp_term_relationships AS tt17 ON (wp_posts.ID = tt17.object_id) 
WHERE 1=1 AND (
 (
 wp_term_relationships.term_taxonomy_id IN (8)
 AND
 tt1.term_taxonomy_id IN (28)
 AND
 tt2.term_taxonomy_id IN (59)
 )
 OR
 (
 tt3.term_taxonomy_id IN (8)
 AND
 tt4.term_taxonomy_id IN (28)
 AND
 tt5.term_taxonomy_id IN (20)
 )
 OR
 (
 tt6.term_taxonomy_id IN (6)
 AND
 tt7.term_taxonomy_id IN (28)
 AND
 tt8.term_taxonomy_id IN (59)
 )
 OR
 (
 tt9.term_taxonomy_id IN (6)
 AND
 tt10.term_taxonomy_id IN (28)
 AND
 tt11.term_taxonomy_id IN (20)
 )
 OR
 (
 tt12.term_taxonomy_id IN (7)
 AND
 tt13.term_taxonomy_id IN (28)
 AND
 tt14.term_taxonomy_id IN (59)
 )
 OR
 (
 tt15.term_taxonomy_id IN (7)
 AND
 tt16.term_taxonomy_id IN (28)
 AND
 tt17.term_taxonomy_id IN (20)
 )
 ) AND wp_posts.post_type = 'gallery' 
   AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending') 
   

该查询正在执行，站点已关闭。不知道它在WordPress上从哪里执行。在主题或任何自定义代码或插件上都没有编写这样的查询。是WordPress上的bug吗？

Answer 1

您可以删除一些不需要的左联接,

并且不要在(x)中使用，因为只有一个x。

SELECT 
    COUNT(*) 
FROM wp_posts 
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
LEFT JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) 
LEFT JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id) 
LEFT JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id) 
LEFT JOIN wp_term_relationships AS tt5 ON (wp_posts.ID = tt5.object_id) 
LEFT JOIN wp_term_relationships AS tt6 ON (wp_posts.ID = tt6.object_id) 
LEFT JOIN wp_term_relationships AS tt8 ON (wp_posts.ID = tt8.object_id) 
LEFT JOIN wp_term_relationships AS tt12 ON (wp_posts.ID = tt12.object_id) 
WHERE (
 ( wp_term_relationships.term_taxonomy_id = (8) AND tt1.term_taxonomy_id = (28) AND tt2.term_taxonomy_id = (59) )
 OR
 ( tt3.term_taxonomy_id = (8) AND tt1.term_taxonomy_id = (28) AND tt5.term_taxonomy_id = (20) )
 OR
 ( tt6.term_taxonomy_id = (6) AND tt1.term_taxonomy_id = (28) AND tt8.term_taxonomy_id = (59) )
 OR
 ( tt6.term_taxonomy_id = (6) AND tt1.term_taxonomy_id = (28) AND tt5.term_taxonomy_id = (20) )
 OR
 ( tt12.term_taxonomy_id = (7) AND tt1.term_taxonomy_id = (28) AND tt8.term_taxonomy_id = (59) )
 OR
 ( tt12.term_taxonomy_id = (7) AND tt1.term_taxonomy_id = (28) AND tt5.term_taxonomy_id = (20) )
 ) AND wp_posts.post_type = 'gallery' 
   AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending') 
  

编辑：就像Strawberry说的，这也应该起作用：

SELECT 
    COUNT(*) 
FROM wp_posts 
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id and tt1.tem_taxonomy_id IN (6,7,8)) 
INNER JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id and tt1.tem_taxonomy_id=28) 
INNER JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id and tt1.tem_taxonomy_id IN (20,59))  
WHERE  wp_posts.post_type = 'gallery' 
   AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending')

Answer 2

这是一个查询，以找出在某种图像库中有多少项，这些项与某些类别相匹配。这是一个由代码创建的查询，可能是在需要时动态创建的。

我想你不是很容易解决WordPress代码的问题。即使是这样，在代码中找到这个查询的起源也会让xxx头疼。

您可能需要清理WordPress数据库表。当你使用WordPress时，它的表会积聚一些旧的帖子、评论、垃圾邮件和其他东西。一个大型的WordPress安装数据库可以积累惊人的数量，这样的问题。有一些插件可以进行清理，比如这个插件。

您的查询包括其中....wp_posts.post_type='gallery'。这意味着一个图像库插件或主题功能。你可能想看看你的插件和主题，看看你是否可以识别一个，然后联系它的开发人员寻求帮助。

如果您有许多图库项目被组织成一组复杂的类别，您可能想要研究如何简化这些类别。