Hi Trevor,

Thanks for responding after hours.

Regarding your suggestion to add individual post to the cache after each iteration: that seems like exactly what we did in APPROACH#2. Please refer my original post to see issues that we ran into with that.

Our blog is hosted in AWS cloud on a t2.small instance.

If we potentially switch to more resourceful server, which PHP settings do you recommend to change?

Hello again,

So we were able to significantly lower the page load time by disabling Auto Count setting in S&F.
For 30 000 posts the load time of a page with S&F widget went down from 6 minutes (!!) to 7 seconds.

As per our our conversation, we are considering chunking our posts in smaller groups. We ran some performance tests for 5 000 posts with Auto Count disabled. Here are the results:
Load page with S&F widget ~3.6 sec
Perform a search ~27 seconds

While this timings are looking much better, 27 seconds is way too slow for searching 5 thousand posts.
Is there anything else we are missing?

Some info about our 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
S&F Pro 4.6.1
MySQL 5.6.27

php max_memory_limit 256M;
WP_MEMORY_LIMIT 256M;
WP_MAX_MEMORY_LIMIT 256M;

Snippet from S&F System Status:
Database
Table wp_search_filter_cache Exists: ✔ 204875 Rows Found
Table wp_search_filter_term_results Exists: ✔ 9916 Rows Found
Total Number of Fields Cached: 4

UPDATE
I did some more research and observed few long running SQL queries that can potentially be improved.
These get executed every with every search:

1. Searching postmeta

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;

This query is the biggest troublemaker and accounts for about 65% of our search time.
How to improve: We have 18 post meta records for each post of our custom post type.
However, we only need 4 of them to be searchable. So when I changed the query to look something like this:

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
        <em>AND
        wp_postmeta.meta_key in ('custom_field1',
                                'custom_field2',
                                'custom_field3',
                                'custom_field4')</em> 
       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;

it ran 2 times faster. I didn’t find an option to exclude certain post meta fields from search in the plugin settings. If there is a way to do that, please point me.

2. Queries to search and filter cache

SELECT DISTINCT field_value
    FROM wp_search_filter_cache
    WHERE field_name = '_sfm_customfield1'

SELECT DISTINCT field_value
    FROM wp_search_filter_cache
    WHERE field_name = '_sfm_customfield2'

SELECT DISTINCT field_value
    FROM wp_search_filter_cache
    WHERE field_name = '_sfm_customfield3'

SELECT DISTINCT post_id 
    FROM wp_search_filter_cache

SELECT DISTINCT field_value_num as field_value
    FROM wp_search_filter_cache
    WHERE field_name = '_sft_post_tag'

These are pretty lengthy too and sum up to ~2 seconds. I don’t see a purpose of executing these every time we search though. Could you please comment on this?

Thank you