Shopware 6 How to improve Performance of the Order Search in the Backend?

user_6307355 0
Viewed 41

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?

shopware6
1 Answers

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 general, I think they did a really bad job with all the backend searches, rendering many of it unusable, especially if you have a lot of data. Probably the most disappointing thing in SW6...

In our old, pre-SW6 platform we had serious performance issues due to huge huge tables, too. 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.

user_2701890
Related