I am using Shopware 6.4.6.1
I have about 3 Mil. Orders and Customers in the Database.
When I use the backend to search for a specific order number, the system fires a very complex query against the database which is way too slow to respond in time, so I get a timeout. This way, it is not possible to use the backend to find orders. The database does not have any indexes on the tables used in the query, what I find very strange.
As far as I understand, the elasticsearch cluster is only used for products, categories and the like, not to find orders and customers.
What is the general concept in SW6 to find a specific order / customer in a performant way that will work with a few million orders / customers?
No code here, just a general answer.
The best solution is to setup an Elasticsearch Cluster (or complete ELK stack for that matter). You will need a custom indexer for this (to tell Shopware which fields you want in Elastic), and decorate/extend the Core ApiControllers' search() method with something like this:
if (in_array($entityName,['order','customer'])) {
$context->addState(Context::STATE_ELASTICSEARCH_AWARE);
}
We have made a custom extension to Shopware 6 and added customer and order data to Elsaticsearch, so this makes searches extremely snappy and fast. Just like product searches in SW6.
A less promising solution is to add proper DB indexes to MySQL. This speeded up my order searches by about 30-40%. I cannot grasp why Shopware ships its DB without indexes in the first place, maybe it has to do with the necessity to re-build indexes on writes and this consuming quite some memory. No idea. One should review the indexes of all the tables involved.
In our case, the backend searches performed very poorly. Probably this is only a problem if you use them. So we were forced to fix this with Elasticsearch because support staff needs to find customers quickly.
In our old, pre-SW6 platform (WooCommerce) we had serious performance issues due to huge huge tables, as well. There the solution was to add an additional order_lookup table to the DB with only a minimal set of columns (mostly concatenated strings, and only fields customer support really searches in). This table had optimised indexes, data was written into it with MySQL triggers each time a new order was added or edited. In the backend a custom made search page queried the order_lookup table. You could do something similar in SW6 if Elasticsearch is not for you.