Additional Keys for Ecommerce Store

  • Resolved yasin939

    (@yasin939)


    2 years, 9 months ago

    Would it be possible to add keys for an eCommerce Store that has product filtering with lots of attributes?

    wp_terms
    wp_term_taxonomy
    wp_term_relationships
    wp_woocommerce_termmeta
    wp_woocommerce_attribute_taxonomies (for product attributes)

    We have a website with lots of attributes and performance is too slow when doing product filtering on a category page.

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

    (@olliejones)

    Thanks for the suggestion. We’re always looking for more tables and columns needing indexing.

    Can you please help us figure out the right indexes to use? Use the plugin’s Monitor feature to

    1. start a monitor

    2. perform some of the “too slow” operations (views of the category pages, filters, etc) while the monitor is active.

    3. Wait for the monitor’s time to expire.

    4. Upload the monitor to our server and tell us the upload id in this support thread.

    Thanks.

    • This reply was modified 2 years, 9 months ago by OllieJones.
    Plugin Author OllieJones

    (@olliejones)

    This enhancement request is in

    https://github.com/OllieJones/index-wp-mysql-for-speed/issues/24

    Thanks for getting in touch about it.

    Thread Starter yasin939

    (@yasin939)

    The monitor is uploaded with ID DTpgsCCU

    Looking forward to getting the extremely slow product filtering issue resolved. As more and more filters are added it slows down to up to 20-25 seconds.

    Website Link Tested on : https://yasam.co.uk/product-category/bedroom-furniture/wardrobe/

    Thread Starter yasin939

    (@yasin939)

    The previous upload was with the following hardware config:
    4GB RAM
    2 CPU Core

    I did run monitor again after bumping up a server to the following:
    8GB RAM
    4 CPU Core

    Upload ID is still the same DTpgsCCU

    I hope it has captured what is required in the same ID.

    After upgrading the server, query speed has increased but feel it can still be optimized by correct indexing.

    Looking forward to an update on your findings.

    Just to make you aware page caching is disabled on all category pages and a link to one of the category page is given in the previous message.

    If need any more info please do reach out to me and will be more than happy to provide required details.

    Thread Starter yasin939

    (@yasin939)

    I think when doing product filtering it performs queries with JOINs and to speed up those queries we also need to index all relevant tables (As raised in this thread).

    pa_{attribute} – Product Attributes for filtering.

    Ran mysqltuner.pl and get the following:
    Joins performed without indexes: 271
    join_buffer_size (> 256.0K, or always use indexes with JOINs)

    As you can see it’s asking to use indexes for all JOINs.

    Hope this helps to add keys to Tables where Product Attributes are stored.

    Great work yasin and team!

    I am developing a site (migrated from Magento) with over 10,000 products and 80 attribute based filters.

    Obviously it was wrongly designed (the Magento developer) instead of creating categories was using (far too many) filters. I have managed to reduce the attribute based filters to 40 and using WOOF as the filter mechanism of choice. Yet indexes are much needed (even for the update of WOOFs own index maintenance).

    I am looking forward to your progress. If you need to use my database/project as your testbed I will be happy to oblige!

    • This reply was modified 2 years, 9 months ago by dimitrisv. Reason: spelling mistakes
Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Additional Keys for Ecommerce Store’ is closed to new replies.