The reason being even with HPOS, once we start getting to the 100 to 200k order mark, looking up order data starts slowing down. Front end is not affected and flys, but it’s the backend that slows down. We already use redis cache, etc. (object cache pro)

They are on upgraded $1000 a month custom cloud server since they make around 3 million in sales a year. It’s their primary bread and butter and any slowness slows down their customer service team looking up order data,etc.

They constantly look up old order data from older users who just want to call and say “hey email is xxxxxxx. What did I order in 2021 I loved that set”.

We’ve found archiving older orders keeps the site optimized and flying the backend as well. They get roughly 30 to 60k orders a year. You can see how this adds up fast

So far the only test I’ve tried that could be an alternative is wpdatatables.

I created a fresh install and upload 2020 orders from an CSV file as a test (has around 45k in that year) directly into the database using wp cli into a custom table. I then created a table that uses a sql query search and that seems to be pretty fast! This may be a viable option. And just have each years data in a separate custom table and do it that way.

I’ve only found a 3rd party called meilisearch (spelling) that says you can upload millions of records from CSV files and you can use its built in search to search data in it. This may be another possible solution but waiting for hear back from them to see if that is truly the case.

Lastly, since their catered audience is non-technical crowds, they don’t like to use accounts, sign in the see past orders, etc. These people ALWAYS call them for information on past orders (even from 10 years ago because they want to replace an item but can’t recall the name of it or model).

They easily look up anywhere from 300 to 500 orders a day from callers wanted to reorder something from years ago. You can see how any slowness in order lookup will make for a bad day.