[5.x]: Searching orders by customer name or email is really slow

[MatthiasBrodelet]

What happened?

Description

On the orders overview, searching by typing a customer name or email in the search input field is really slow.
It takes 10 seconds just to get the results when searching for "peter".
After 10 seconds I get 3 matches but the range / pager is still loading for another 3 seconds just to show "1–3 of 3 orders".
Searching for order references is really quick so I think this must be an optimisation thing.

Something similar happens when I try to filter the orders based on "Customer".
I have more than 1000 customer accounts so I need to use the search field to find this specific user.
The search takes really long, I think it will also be about 10 seconds.
When I have found the customer and apply the filter everything is super quick.
It really seems to be a search issue and not a filter issue.

Imagine you are speaking to a customer on the phone and want to find their order quickly in order to help them.

Steps to reproduce

1. Create 1000 unique users and 110 orders
2. Try searching the orders by using the search input field by the name of a specific customer

Expected behavior

The loading should not take 10 seconds.
1 or 2 seconds is acceptable.

Actual behavior

Searching takes a really long time and hinders the workflow.

Craft CMS version

5.x

Craft Commerce version

5.x

PHP version

8.2

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[linear]

PT-2182 [5.x]: Searching orders by customer name or email is really slow

[nfourtythree]

Hi @MatthiasBrodelet

Thank you for your feedback, can I just check which database engine (and version) you are using, MySQL, MariaDB, Postgres?

Thanks

[MatthiasBrodelet]

@nfourtythree I am running a MySQL 8.4 database.

[lukeholder]

@MatthiasBrodelet can you email your DB dump, composer.json and composer.lock files to support@craftcms.com and reference this issue. We will see if we can reproduce the performance issue.

[MatthiasBrodelet]

@lukeholder I just sent everything over + some more information.