Add keys to wp_aws_index ?

  • Resolved idigmarketing

    (@idigmarketing)


    2 years, 6 months ago

    I have a site with about 30k skus & alot of complexity to the data. We are using this plugin to run the search but its still very slow slow even after server upgrade & a while slew of other optimizations to the site:
    https://advanced-woo-search.com/?utm_source=wp-repo&utm_medium=listing&utm_campaign=aws-repo

    Product cats with thousands of products render relatively quickly but the search is very slow. I’m wondering if I can add keys to the index table to speed it up?
    It has abotu 750k rows & is 158 mb in size…

    I have lots of data from the monitor but not entirely sure how to read it. I just know our search is still too slow having done all we have to try and speed it up.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author OllieJones

    (@olliejones)

    Thanks for the heads up on this Advanced Woo Search with the unfortunate acronym (confusing it with a large server-rental company). I will look.

    If you captured a monitor with my plugin, may I suggest you upload it so I can take a look? Please reply with the upload id.

    Thread Starter idigmarketing

    (@idigmarketing)

    w4spUKxk < here you go

    Yes it doesn’t seem to be very advanced lol

    Plugin Author OllieJones

    (@olliejones)

    Oh my my my. This Advanced WooCommerce Search plugin contains the single most notorious SQL performance killer.

    It builds a wp_aws_index table with many rows, each containing a search term. For example, it might have the terms “red”, “white”, “tee”, “shirt”, “L” in it if you were selling tee-shirts. That’s fine.

    But then it searches it with SQL like this.

    SELECT DISTINCT ID, 
       SUM( /* a complex weighting expression */) AS relevance
FROM wp_aws_index
WHERE 1=1 
  AND (term LIKE '%red%'))
  AND visibility NOT IN ('hidden', 'catalog')
GROUP BY ID
HAVING relevance > 0
ORDER BY relevance DESC, id DESC
LIMIT 0, 10

    WHERE term LIKE '%red%' !! ?? Really? It causes a full index scan on the table, examining every row.

    There’s not much indexing that can fix that particular pessimization (opposite of optimization). It would take a rewrite of the plugin.

    Try the Relevanssi plugin. It doesn’t do that. It does something clever with search terms with reversed characters.

    Thread Starter idigmarketing

    (@idigmarketing)

    I tried Relevanssi really hoping it would be the cure but I disabled my other search plugin, removed the custom functions that were making it the right order incase they messed with Relevanssi & then installed, set up & indexed but when I do a search on the front end it returns No results found… not sure why

    I enabled the backend search as well that works but not all that fast compared to how it was its maybe 15% faster at best. But would like to see it work on front end to be sure…

    https://sbsimpson.com/relevannssi-test/

    I should move this to their forum sorry just FYI.. .appreciate the advice but I can’t seem to get it going

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Add keys to wp_aws_index ?’ is closed to new replies.