Gracefully import 30000 posts

Grosvenor

#60371

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

Add New Note to this Reply

Ross Moderator

#60518

Hi Rob

Thanks for your extensive research in to this – you’re really getting into the nitty gritty of S&F code 🙂

I’ve noticed recently that fields with a high number of options really seem to slow things down, which of course means some bad optimizations – I think you’ve highlighted one above.

Most of what you mention makes sense, I’m working on these areas now and taking a look where things can be improved…

What software/plugin do you use for your performance testing? I’ve been using query monitor – https://en-gb.wordpress.org/plugins/query-monitor/ but generally testing with datasets much smaller than yours.

Best

Add New Note to this Reply

Ross Moderator

#60522

BTW, as a better way to handle large fields, it might be best to have a autocomplete field, where a user starts typing and gets a list of suggestions? Do you think that would work in your scenario?

This is something I’m planning on adding in the very near future.

Best

Add New Note to this Reply

Grosvenor

#60645

Hi Ross,

Glad to hear my input was useful. I mainly just use Chrome DevTools and MySQL’s native Performance Schema via MySQL Workbench for my performance testing.

One other thing that helped to speed up the search query by about 15% is creating a FULLTEXT index on wp_postmeta(meta_value) and using it in a query. So if you could include this feature in your upcoming performance fix – that’d be great.

Also could you please kindly provide a timeframe for these improvements to go live? We’ve put some good effort to tune up our Search & Filter widget and would like to stick to it, at the same time we have our own deadlines to meet.

As far as autocomplete solution – we already partially employ it. The list of available options gets filtered as user types, although if nothing is typed in we prefer all the options to be visible for the user, so they know what they can pick from.

Thanks!

Add New Note to this Reply

Ross Moderator

#61999

Hey Rob

Just to let you know I’ve not forgotten about this, been working on tweaking performance for a while now, testing in different environments etc etc.. still a fair bit to go…

RE the FULLTEXT, do you mean putting on a column in the S&F tables? Because this is where the query is mostly performed, not on the WP Posts table :/

Best

Add New Note to this Reply

Grosvenor

#65138

Hi Ross,

I meant introducing a FULLTEXT index for tables with extensive text values that being searched against. In our case it is wp_postmeta because that’s where we store the contents of our custom post type.
So we ended up building a FULLTEXT index on that table and intercepting all WordPress search queries to modify them to use the index – e. g. replace LIKE with MATCH AGAINST.
At the same time we are excluding unneeded meta fields from the query as mentioned above.

Also I realized that one of the biggest bottlenecks on a page load was generating html for the dropdowns with thousands of items. So I modified class-search-filter-generate-input.php to not do that for fields with certain flag and enqueueing javascripts for lazy load instead. It sounds like something like this will be supported in out of the box in the next release of SF – so will be excited to see that.

One other feature that would be really handy is so called archiving. Like being able to limit search and filter to only posts that satisfy certain condition. For example only posts that were created within last 5 years. And then search, filter and generate filter options accordingly.

Add New Note to this Reply

Ross Moderator

#71095

Hi Rob

I’ve made quite a few changes & speed improvements… I’ve just sent you the beta in case you want to test, this will likely be the next release after a bug finding mission.

Thanks

Add New Note to this Reply

Grosvenor

#79045

Hey Ross, where should I look for beta?

Add New Note to this Reply

Trevor Moderator

#79056

Gosh, that was 6 weeks ago. It would have been sent to the email address you registered with and, as it has a zip attachment, almost certainly into spam or junk, and probably long since deleted 🙁

Is that address OK to use again?

Add New Note to this Reply

Grosvenor

#79168

This reply has been marked as private.

Add New Note to this Reply

[Author]

Posts