Support Forums

The forums are closed and will be removed when we launch our new site.

Looking for support? You can access the support system via your account.

Grosvenor

Forum Replies Created

Viewing 10 posts - 1 through 10 (of 38 total)
1 2 3 4
  • Grosvenor in reply to:
    Is 2.3.2 compatible with memcached?
    (Private)  March 8, 2017 at 4:51 pm #95545

    Hi Trevor,

    Do you have a reason to suspect that 2.3 might need more memory than 2.2?

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  December 29, 2016 at 4:35 pm #79174
    This reply has been marked as private.
    Grosvenor in reply to:
    post_date filter not working with open ranges
    (Private)  December 29, 2016 at 3:39 pm #79170

    Using this filter to hook in custom logic, thanks!

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  December 29, 2016 at 3:38 pm #79168
    This reply has been marked as private.
    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  December 28, 2016 at 11:18 pm #79045

    Hey Ross, where should I look for beta?

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  October 19, 2016 at 5:57 pm #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.

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  September 27, 2016 at 5:46 pm #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!

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (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

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  September 23, 2016 at 3:32 pm #59756

    Hey Ross,

    Yes, we have 4 custom fields with the options count varying from few hundred to few thousand.

    We do need all of them be available though. Is there a more efficient way to handle such fields?

    Grosvenor in reply to:
    Gracefully import 30000 posts
    (Private)  September 22, 2016 at 2:02 am #59404

    UPDATE:
    We use “Search Everything” plugin to control which content should be searchable. Looking closer at its configuration we disabled “Search every custom field” setting because our custom fields are already searchable (thanks to S&F?). That decreased the search time drastically.

    5000 posts
    Load page with S&F widget: ~4 sec
    Perform search: ~8 sec

    30000 posts
    Load page with S&F widget: ~7 sec
    Perform search: ~18 sec

    Our blog is usable again, although these timings are still far from a great user experience. Any ideas on how to further improve search performance are much appreciated.

  • Author
    Posts
Viewing 10 posts - 1 through 10 (of 38 total)
1 2 3 4