7

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?

  • 4
    This is an opinion: WordPress' full-text search will always be less efficient and slower than a dedicated search solution. Try ElasticSearch, Google Site Search, Swiftype or Algolia. – Florian Sep 27 '16 at 4:21
1

It sounds like you're already on the right track, adding additional indexes and tweaking the query. I've found MySQL's EXPLAIN syntax to be helpful, giving me a good sense of where in the query things might be going awry.

From a high-level, it appears that the query has a lot of joined tables, LIKE comparisons (with wildcards), and OR comparators, all of which are symptomatic of potentially less-than-performant queries. I'd ensure that each of those columns are indexed properly (especially those that perhaps aren't normally queried), and see if you've missed anything there.

Overall database size can be a factor in some environments; at one point, a client site was complaining about slow performance (not just search) and we discovered that indexes were choking on the fact that about 20% of the database was made up of post revisions (this was a daily news site, so revisions on a post from six months ago wasn't really relevant). Revision Strike was born out of that need, and had a huge impact on general – as well as search – site performance.

Another option to consider is something based on a more dedicated search technology. ElasticPress (full disclosure: I work for 10up, the maintainer of ElasticPress) ties Elasticsearch into WordPress queries, significantly reducing the load on your web server while making it possible to do more complicated searches (like you appear to be doing with your search plugins).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.