Wordpress - WP Slow search SQL on one of the servers

errorous
November 7, 2023
290 views
0 votes
2 Answers

I have 2 dedicated servers, S1, 24 cores and 128gb RAM, and S2, 8 cores and 64GB RAM. Both server CPUs have multithreading. On S1 I run cPanel and S2 serves only as a DB server( remote ). Both servers are running MySQL 8, but S1 has community edition that is installed with cPanel.

The problem is in ( basic ) WP search query.

SELECT 
  SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID 
FROM 
  wp_posts 
  INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID) 
  LEFT JOIN wp_postmeta AS b ON (
    (
      (b.post_id = a.post_id)
    ) 
    AND (
      (
        b.meta_key LIKE CONCAT('_', a.meta_key)
      )
    )
  ) 
WHERE 
  1 = 1 
  AND (
    wp_posts.ID NOT IN (
      SELECT 
        object_id 
      FROM 
        wp_term_relationships 
      WHERE 
        term_taxonomy_id IN (1427, 1428, 1429)
    )
  ) 
  AND (
    (
      (b.meta_id IS NOT NULL) 
      AND (a.meta_value LIKE '%test%')
    ) 
    OR (
      (
        wp_posts.post_title LIKE '%test%'
      ) 
      OR (
        wp_posts.post_content LIKE '%test%'
      ) 
      OR (
        wp_posts.post_excerpt LIKE '%test%'
      )
    )
  ) 
  AND (
    (
      wp_posts.post_type = 'post' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'attachment' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'book' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'sfwd-courses' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'groups' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'library' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'topic' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'series' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'videos' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'material' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'authors' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    )
  ) 
GROUP BY 
  wp_posts.ID 
ORDER BY 
  wp_posts.post_title LIKE '%test%' DESC, 
  wp_posts.post_date DESC 
LIMIT 
  0, 12

Note that this query is generated by WP, and there are no additional plugins nor is there any code to modify this query. With that said, I can’t change the query itself.

Now, WP is installed on S1, and is calling S2 to run SQL queries. This very search takes 4.5s on S1 and 39s on S2. Please note that this is not a connectivity issue, as I’ve tested said query by logging onto S2, running mysql console, and run query directly on the server.

I’ve also checked whether there’s an issue with INDEXES, but INDEXES are the same on both servers. What could be the issue?

EDIT: Turns out it migration of the database went wrong. Here’s full EXPLAIN and SHOW CREATE TABLE for both staging( the one that works well ) and production( the one with slow query ).

SERVER 1( SLOW QUERY ):

CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=2058873 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=64892133 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


explain SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta AS a
ON ( a.post_id = wp_posts.ID )
LEFT JOIN wp_postmeta AS b
ON ( (( b.post_id = a.post_id ))
AND (( b.meta_key LIKE CONCAT( '_', a.meta_key ) )) )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (1427,1428,1429) ) )
AND ( ( ( b.meta_id IS NOT NULL )
AND ( a.meta_value LIKE '%test%' ) )
OR ( ( wp_posts.post_title LIKE '%test%' )
OR ( wp_posts.post_content LIKE '%test%' )
OR ( wp_posts.post_excerpt LIKE '%test%' ) ) )
AND ((wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'acf-disabled'
OR wp_posts.post_status = 'graded'
OR wp_posts.post_status = 'not_graded'
OR wp_posts.post_status = 'private'))
OR (wp_posts.post_type = 'attachment'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_statu[...]

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	NULL	ALL	post_id	NULL	NULL	NULL	870451	Using temporary; Using filesort
1	SIMPLE	wp_posts	NULL	eq_ref	PRIMARY,post_name,type_status_date,post_parent,post_author	PRIMARY	8	proddb.a.post_id	1	Using where
1	SIMPLE		NULL	eq_ref	<auto_distinct_key>	<auto_distinct_key>	9	proddb.a.post_id	1	Using where; Not exists
1	SIMPLE	b	NULL	ref	post_id	post_id	8	proddb.a.post_id	40	Using where
2	MATERIALIZED	wp_term_relationships	NULL	ALL	PRIMARY,term_taxonomy_id	NULL	NULL	NULL	43340	Using where

SERVER 2( regular query time ):

CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=2058548 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


CREATE TABLE `wp_postmeta` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=64885169 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta AS a
ON ( a.post_id = wp_posts.ID )
LEFT JOIN wp_postmeta AS b
ON ( (( b.post_id = a.post_id ))
AND (( b.meta_key LIKE CONCAT( '_', a.meta_key ) )) )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (1427,1428,1429) ) )
AND ( ( ( b.meta_id IS NOT NULL )
AND ( a.meta_value LIKE '%test%' ) )
OR ( ( wp_posts.post_title LIKE '%test%' )
OR ( wp_posts.post_content LIKE '%test%' )
OR ( wp_posts.post_excerpt LIKE '%test%' ) ) )
AND ((wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'acf-disabled'
OR wp_posts.post_status = 'graded'
OR wp_posts.post_status = 'not_graded'
OR wp_posts.post_status = 'private'))
OR (wp_posts.post_type = 'attachment'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_statu[...]

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp_posts	NULL	index	PRIMARY,post_name,type_status_date,post_parent,post_author	PRIMARY	8	NULL	33523	Using where; Using temporary; Using filesort
1	SIMPLE		NULL	eq_ref	<auto_distinct_key>	<auto_distinct_key>	9	stagedb.wp_posts.ID	1	Using where; Not exists
1	SIMPLE	a	NULL	ref	post_id	post_id	8	stagedb.wp_posts.ID	17	Using where
1	SIMPLE	b	NULL	ref	post_id	post_id	8	stagedb.wp_posts.ID	17	Using where
2	MATERIALIZED	wp_term_relationships	NULL	ALL	PRIMARY,term_taxonomy_id	NULL	NULL	NULL	41970	Using where

Answers

- BillKarwin
- November 7, 2023 at 1:23 am
- 0 votes
0
You can see the rows field in the first row of each EXPLAIN report is quite different. 870k rows examined in the slow version, versus 33k rows examined in the one that performs better.
```
1   SIMPLE  a           NULL    ALL     post_id NULL     NULL NULL    870451  100.00  Using temporary; Using filesort
                                                                      ^^^^^^
...

1   SIMPLE  wp_posts    NULL    index   PRIMARY  PRIMARY 8    NULL    33523   10.14   Using where; Using temporary; Using filesort
                                                                      ^^^^^
...
```
Even if data is held in RAM in the buffer pool, performance is usually proportional to rows examined (assuming other parts of the query execution are the same, like use of temp tables).

The first thing I’d try is ANALYZE TABLE for each of the tables in the query. This may or may not help, but it’s easy to do, and usually safe to run any time, even on a busy server. This will refresh index statistics that the optimizer uses to choose indexes, so it might fix the problem easily.

You might need to use an index hint to coerce the optimizer into preferring to use an index, instead of resorting to the table-scan.

Further, I notice the last table in the EXPLAIN is doing a table-scan, examining 43k rows. It’s usually bad news when a joined table must do a table-scan, instead of narrowing down its search with the help of an index.
```
2   MATERIALIZED wp_term_relationships NULL ALL ... NULL NULL NULL 43340 100.00  Using where
                                                                   ^^^^^
```
In this case, it says the table access type is MATERIALIZED which means it has saved the result of the subquery to a temp table automatically, so at least it doesn’t have to do the table-scan repeatedly. But it’s still costly to do a table-scan, even if it only does it once.

So I’d suggest adding an index to help the subquery find only the matching rows. I’m assuming your tables are part of a WordPress instance, and I looked up the definition of that table, and I see it’s supposed to have an index on the term_taxonomy_id column already. I suggest you double-check that table to confirm the index is in fact defined.
Login or Signup to reply.

- OJones
- November 7, 2023 at 1:36 pm
- 0 votes
0
The query you showed us is notorious for being slow in large WordPress installations.

tl;dr

Here’s a free GPL plugin (by Rick James and me) to upgrade the indexes on your tables to make this kind of query faster. https://wordpress.org/plugins/index-wp-mysql-for-speed/

Your sample query also implements WordPress’s search feature. Core WordPress does its search with the notoriously slow SQL column LIKE '%searchterm%' query pattern. That’s, simply stated, an obtuse way of doing search suitable for a site with hundreds of posts / products / pages / whatever. You have more than that. Change the search algorithm with a plugin. I like Relevanssi but there are several other good ones.

Consider adding a persistent object cache plugin.

Longer explanation, index improvement: Queries shaped like this (yours among them)
```
... FROM wp_posts p
    LEFT JOIN wp_postmeta m ON p.ID = m.post_id and m.meta_key = 'something'
```
are slow with WordPress’s standard indexing because of prefix indexes and the lack of compound indexes. Adding a compound index on (post_id, meta_key, meta_id) speeds up these queries. Making that the clustered index helps even more. Our plugin changes your site’s indexes to do that.

Longer explanation, search: When your user types something in WordPress’s search box it turns into SQL shaped like this
```
    column_a LIKE '%something%'
 OR column_b LIKE '%something%'
 OR column_c LIKE '%something%'
```
with a leading % wildcard. This isn’t sargable so the database server scans through the whole posts table. (See you after lunch, eh?) Your only recourse here is to install a search plugin that uses a better algorithm. Relevanssi splits your content into individual words so it can avoid that %. Other search plugins do similar things. They can be installed without requiring changes to your theme or pages, and speed up search under the covers.

Longer explanation, persistent object cache: Most sites have a few queries like yours that get run a lot. For example, a query like yours displays the front door page of many online stores. The persistent object cache hangs on to the results of those queries and uses them when new users ask for the same information.

One more thing. MySQL’s use of SELECT SQL_CALC_FOUND_ROWS ... LIMIT start, count is also a performance killer on large sites because of the SQL_CALC_FOUND_ROWS modifier. I have a very simple experimental not-yet-published plugin to put the results of that modifier into the persistent object cache, here. If you want to try it you’re welcome to it.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

WordPress – WP Slow search SQL on one of the servers

Answers