带ORDER BY的WordPress MySQL查询过慢问题求助（附SQL语句）

Hey there! Let's break down why your query slows down so drastically when adding that ORDER BY post.post_date DESC clause, and fix it step by step.

First, the core issue here is the filesort operation MySQL has to perform. Without the ORDER BY, MySQL can just return results as it finds them through the joins. But with the sort, it has to fetch all matching rows first, load them into memory (or disk if the result set is too big), sort them, and then grab the top 35. That's where the 10-second delay comes from.

Let's start with index fixes (the quickest wins) #

1. Optimize the wp_postmeta index
   Your query relies heavily on joining wp_postmeta where meta_key = 'p2m' and meta_value = product.ID. Right now, MySQL is probably scanning a lot of rows to find those matches. Create a composite index for this exact condition:

   CREATE INDEX idx_meta_key_value ON wp_postmeta (meta_key, meta_value);
   

   This will let MySQL jump straight to the rows it needs for the join, cutting down the initial result set size significantly.

2. Fix the filesort with a post_date index
   The type_status_date index you mentioned is for the product alias of wp_posts, but your sort is on the other wp_posts table (the post alias). That index doesn't help here. Create an index specifically for sorting the post dates:

   CREATE INDEX idx_post_date_desc ON wp_posts (post_date DESC);
   

   If most of your posts are published (which they usually are in WordPress), you can make it even more efficient by adding post_status to the index:

   CREATE INDEX idx_post_date_status_desc ON wp_posts (post_date DESC, post_status);
   

   This tells MySQL it can use the index to both filter (if you add a post_status condition later) and sort, eliminating the filesort entirely.

Next, tweak the query logic for bigger gains #

Your current query joins all tables first, then sorts the entire result set. We can flip that around to sort first and only join the top 35 rows we need:

SELECT 
  product.ID productId, product.guid productLink, product.post_title productTitle,
  post.ID postId, post.post_title postTitle, post.post_content postContent, post.post_date postDate,
  tm.slug typeSlug, tm.name typeName, tm2.slug langSlug, tm2.name langName, tm3.slug pubSlug, tm3.name pubName,
  IFNULL(wl.id,0) wishlist 
FROM (
  -- First get the latest 35 posts that have a matching product via p2m meta
  SELECT ID, post_title, post_content, post_date 
  FROM wp_posts 
  WHERE EXISTS (
    SELECT 1 FROM wp_postmeta WHERE meta_key='p2m' AND post_id=wp_posts.ID
  )
  ORDER BY post_date DESC LIMIT 0,35
) post
-- Now join only those 35 posts to the rest of the tables
JOIN wp_postmeta meta ON meta.post_id = post.ID AND meta.meta_key='p2m'
JOIN wp_posts product ON product.ID = meta.meta_value AND product.post_type='product'
JOIN wp_term_relationships tr ON tr.object_id = product.ID
JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy='mtype'
JOIN wp_terms tm ON tm.term_id = tt.term_id
JOIN wp_term_relationships tr2 ON tr2.object_id = product.ID
JOIN wp_term_taxonomy tt2 ON tt2.term_taxonomy_id = tr2.term_taxonomy_id AND tt2.taxonomy='language'
JOIN wp_terms tm2 ON tm2.term_id = tt2.term_id
JOIN wp_term_relationships tr3 ON tr3.object_id = product.ID
JOIN wp_term_taxonomy tt3 ON tt3.term_taxonomy_id = tr3.term_taxonomy_id AND tt3.taxonomy='publisher'
JOIN wp_terms tm3 ON tm3.term_id = tt3.term_id
LEFT JOIN wp_yith_wcwl wl ON wl.user_id=1 AND wl.prod_id=product.ID AND wl.post_id=post.ID
ORDER BY post.post_date DESC;

By using a subquery to fetch just the 35 latest posts first, we reduce all subsequent joins and operations to only those rows—this should cut the runtime way down, even without the new indexes.

How to verify it's working #

After making these changes, run EXPLAIN on your query again. Look for:

• No Using filesort in the Extra column
• Lower numbers in the rows column (meaning MySQL is scanning fewer rows)

Content of the question comes from Stack Exchange, question author: Ali