Our WordPress blog was running just fine until we imported about 30k posts. The search on the website became very slow after that.
It now takes about:
- 4 seconds to load a page with Search & Filter widget on it.
- 18 seconds to return search results.
Major plugins that we use:
Custom Post Types - we have just one custom posts type that is used in blog.
Advanced Custom fields - we have a set of custom fields that are searchable and filterable.
Search plugins that we use:
Search Everything - we have Tags, Authors and Comments enabled for search. Post content, title and custom fields are searchable by default.
Search & Filter Pro - used to build our search and filter widget and set filtering rules.
Caching:
We are using Memcache as a pluggable cache for WP as well as persistent cache solution.
Environment:
Physical server: AWS t2.small; 2 GB memory; CPU 1 core up to 3.3 GHz
OS: Windows Server 2012
Webserver: IIS 8.5
PHP 5.6.22
Wordpress 4.6.1
MySQL 5.6.27 (dedicated RDS instance)
We have a pretty limited userbase and not observing significant spikes in server resources usage for both app server and db server.
Here is the longest running SQL query that gets executed when user performing a search on a website:
SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta
ON wp_posts.id = wp_postmeta.post_id
LEFT JOIN wp_term_relationships AS trel
ON ( wp_posts.id = trel.object_id )
LEFT JOIN wp_term_taxonomy AS ttax
ON ( ( ttax.taxonomy = 'post_tag' )
AND trel.term_taxonomy_id = ttax.term_taxonomy_id )
LEFT JOIN wp_terms AS tter
ON ( ttax.term_id = tter.term_id )
LEFT JOIN wp_comments AS cmt
ON ( cmt.comment_post_id = wp_posts.id )
LEFT JOIN wp_users AS u
ON ( wp_posts.post_author = u.id )
WHERE 1 = 1
AND ( ( wp_posts.id IN (<LIST_OF_POST_IDS>)
AND (( (( ( wp_posts.post_title LIKE '%searchterm%' )
OR ( wp_postmeta.meta_value LIKE '%searchterm%' )
OR ( wp_posts.post_content LIKE '%searchterm%' ) ))
OR (( tter.name LIKE '%searchterm%' ))
OR ( (( cmt.comment_content LIKE '%searchterm%' ))
AND cmt.comment_approved = '1' )
OR (( u.display_name LIKE '%searchterm%' )) ))
AND wp_posts.post_type = 'generalpost'
AND (( wp_posts.post_status = 'publish' )) )
AND post_type != 'revision' )
AND post_status != 'future'
ORDER BY wp_posts.post_date DESC
LIMIT 0, 15;
Please see Wordpress DB documentation for a schema reference
This query is responsible for about 65% of search result page load time (12 seconds out of 18 seconds)
We only have 1 custom post type at this point, and only that needs to be searchable. Each custom post type has 18 records in wp_postmeta. Out of those 18 fields only 4 need to be searchable - and that is one potential way to speed up the search.
In my tests adding this clause
`AND meta_key in ('cust_field1', 'cust_field2', 'cust_field3', 'cust_field4',)`
actually sped up the query almost twice. Plugin developers were contacted to implement a feature to support meta fields exclusion.
I have also created a compound index on post_id, meta_key, meta_value columns, which cut down the query time by another 5-10%.
What would be the other ways to make search with this amount of data more efficient so the user experience is not ruined?
