Forums › Forums › Search & Filter Pro › Gracefully import 30000 posts
Tagged: cache, Import, performance
- This topic has 26 replies, 4 voices, and was last updated 7 years, 11 months ago by Trevor.
-
Anonymous(Private) September 26, 2016 at 6:01 pm #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
Ross Moderator(Private) September 27, 2016 at 11:41 am #60518Hi 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
Ross Moderator(Private) September 27, 2016 at 11:43 am #60522BTW, 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
Anonymous(Private) September 27, 2016 at 5:46 pm #60645Hi 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!
Ross Moderator(Private) October 4, 2016 at 7:20 pm #61999Hey 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
Anonymous(Private) October 19, 2016 at 5:57 pm #65138Hi 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.
Ross Moderator(Private) November 18, 2016 at 3:57 pm #71095Hi 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
-
AuthorPosts