Optimize heavy slow query in Wordpress

Question

I have a fairly large database - 162150 total of rows in wp_3_posts table and 521345 total of rows in wp_3_postmeta. I noticed that my site is very slow. I noticed that the slow queries took like 10 seconds to load the page.

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 

I found out that meta_query arguments causes the page slow.

$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 
   );

This is the table I am trying to optimize the meta_query arguments:

$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"));

Is there a way to optimize that?

Answer 1

First

Your question's second query is this, formatted.

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

I think it has a mistake in it. You do an INNER JOIN from posts to postmeta, but you don't include a meta_key filter. That means the query picks up all the postmeta rows for the post in question. That probably isn't right. It definitely makes the query handle a lot of extra data, only to have it removed by your GROUP BY.

Usually postmeta joins look something like this:

 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 always format your queries in your source code so you can read them easily. php allows multiline string constants.

Second

According to LearnDash's help page its table is defined

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)
)

That table could use a change to an index, extending the user_id index to cover your query. You can do this with any MySQL client program. Make a backup first, and do it in a quiet time on your site. But, unless your users each do large numbers of activities, this index probably won't help much.

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

Third

Your indexes on posts and postmeta need changing too. There's a plugin to handle those changes. Index WP MySQL For Speed. It creates indexes to cover queries like yours, described here.