优化 WordPress 中的重慢查询

Question

我有一个相当大的数据库-162150 WP_3_POSTS表中的行总计和521345 WP_3_POSTMETA中的行总数。我注意到我的网站非常慢。我注意到缓慢的查询花了大约10秒钟的时间来加载页面。

SELECT wp_3_posts.*
FROM wp_3_posts
INNER JOIN wp_3_postmeta
ON ( wp_3_posts.ID = wp_3_postmeta.post_id )
WHERE 1=1
AND ( ( wp_3_postmeta.meta_key = 'course_id'
AND wp_3_postmeta.meta_value = '157898' ) )
AND wp_3_posts.post_type IN ('sfwd-lessons', 'sfwd-topic', 'sfwd-quiz')
AND ((wp_3_posts.post_status = 'publish'
OR wp_3_posts.post_status = 'future'
OR wp_3_posts.post_status = 'draft'
OR wp_3_posts.post_status = 'pending'
OR wp_3_posts.post_status = 'private'))
GROUP BY wp_3_posts.ID
ORDER BY wp_3_posts.post_date DESC 

我发现meta_query参数导致页面慢。

$attr_defaults = array(
       'include_outer_wrapper' => 'true',
       'num'                   => false,
       'paged'                 => 1,
       'post_type'             => learndash_get_post_type_slug( 'course' ),
       'post_status'           => 'publish',
       'order'                 => 'DESC',
       'orderby'               => 'ID',
       'cat'                   => '',
       'category_name'         => 0,
       'category__and'         => '',
       'category__in'          => '',
       'category__not_in'      => '',
       'categoryselector'      => '',
       'show_thumbnail'        => 'true',
       'show_content'          => 'true',
       'col'                   => '',
       'progress_bar'          => 'false',
       'array'                 => false,
       'course_grid'           => 'true',
       
       'update_post_term_cache' => false, // don't retrieve post terms
       'update_post_meta_cache' => false, // don't retrieve post meta
       'no_found_rows' => true, // counts posts, remove if pagination required 
   );

这是我试图优化meta_query参数的表：

$enrollquery = $wpdb->get_results( $wpdb->prepare("SELECT Users.ID, Users.user_login, Users.display_name, Learndash.activity_type, PostObject.post_status, PostObject.post_title, PostObject.post_type FROM `wp_users` AS `Users`INNER JOIN `wp_3_learndash_user_activity` AS `Learndash` ON Users.ID = Learndash.user_id INNER JOIN `wp_3_posts` AS `PostObject` ON PostObject.ID = Learndash.post_id INNER JOIN `wp_3_postmeta` AS `Postmeta` ON PostObject.ID = Postmeta.post_id WHERE Users.ID = '".$current_user->ID."' GROUP BY PostObject.ID"));

有没有办法优化它？

Answer 1

首先，

您的问题的第二个查询是格式化。

SELECT Users.ID, Users.user_login, Users.display_name, 
       Learndash.activity_type, 
       PostObject.post_status, PostObject.post_title, PostObject.post_type
  FROM wp_users AS Users
 INNER JOIN wp_3_learndash_user_activity AS Learndash ON Users.ID = Learndash.user_id
 INNER JOIN wp_3_posts AS PostObject ON PostObject.ID = Learndash.post_id
 INNER JOIN wp_3_postmeta AS Postmeta ON PostObject.ID = Postmeta.post_id
 WHERE Users.ID = '".$current_user->ID."'
 GROUP BY PostObject.ID

我认为这有一个错误。您可以从ports到postmeta进行内部加入，但您不包括meta_key filter。这意味着查询会为所讨论的帖子拾取所有postmeta行。那可能不正确。它肯定会使查询处理很多额外的数据，只是通过您的小组将其删除。

通常，postmeta加入看起来像这样的东西：

 INNER JOIN wp_3_postmeta AS Postmeta
        ON PostObject.ID = Postmeta.post_id
       AND Postmeta.meta_key = 'course_id'  /* or some other constant */ 

Pro Tip 始终在源代码中格式化查询，因此您可以轻松阅读它们。 PHP允许多行字符串常数。

第二个

根据 nofollow noreferrer“> learnddash的帮助

CREATE TABLE wp_learndash_user_activity (
  activity_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL DEFAULT '0',
  post_id bigint(20) unsigned NOT NULL DEFAULT '0',
  activity_type varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  activity_status tinyint(1) unsigned DEFAULT '0',
  activity_started int(11) unsigned DEFAULT NULL,
  activity_completed int(11) unsigned DEFAULT NULL,
  activity_updated int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (activity_id),
  KEY user_id (user_id),
  KEY post_id (post_id),
  KEY activity_status (activity_status),
  KEY activity_type (activity_type),
  KEY activity_started (activity_started),
  KEY activity_completed (activity_completed),
  KEY activity_updated (activity_updated)
)

页面使用更改索引，将user_id索引扩展到 cover 您的查询。您可以使用任何MySQL客户端程序执行此操作。 首先进行备份，然后在您的网站上安静的时间进行备份。但是，除非您的用户每个人进行大量活动，否则此索引可能不会有太大帮助。

ALTER TABLE wp_3_learndash_user_activity
    DROP INDEX user_id,
    ADD INDEX user_id (user_id, post_id, activity_type);

第三，

您的索引帖子和postmeta也需要更改。有一个插件来处理这些更改。 index wp mysql for speed 。它创建索引以涵盖像您这样的查询，。