Slow search order by SKU

  • Resolved marinow1991

    (@marinow1991)


    9 months, 3 weeks ago

    Hello!

    I really like your plugin and use it on all my sites.
    On one of my sites that has 20k+ orders, I noticed that the search function in admin panel-> orders -> search search by SKU makes a very slow query and slows down my search query.
    The option is really useful and we use it a lot, but is there a way to optimize it to be a bit faster?

    https://prnt.sc/4V5jX943Qw5U

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Jose Andres Piera

    (@japiera)

    Hi @marinow1991,

    The ATUM Product table is optimized and indexed by product_id, the index used in the SQL query.

    Probably, the excessive delay when searching by SKU is due to your woocommerce_order_itemmeta table’s size.

    Unfortunately, there’s nothing we can do with the WC’s table. You can try deleting orphan data in this table or maybe move old orders to a read-only site…

    Best Regards,
    José Andrés

    Thread Starter marinow1991

    (@marinow1991)

    In my opinion, if you remove the “%” in front of the search element, the query will be greatly simplified.

    If we think about it logically, who searches by SKU, starting from the end of the SKU ??

    For example, if we are looking for orders with the code “42342365342343”
    We can type “423423” but we will never type “342343”

    That would solve the problem :))

    Plugin Author Jose Andres Piera

    (@japiera)

    Hi @marinow1991,

    Ok. That is an optional feature we introduced in ATUM that modifies the WC behaviour. Indeed, we’re pretty sure that they excluded searching by SKU from the order search due to this reason. And we alert in Settings about the possible delay.

    Anyway, you’re free to use the ‘woocommerce_shop_order_search_results’ filter to prevent our code from being executed and execute yours with the modifications you need.

    Best Regards,
    José Andrés

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Slow search order by SKU’ is closed to new replies.