Extremely slow getOrders query because of query complexity

[dylanmaurits]

This piece of code in the searchDataProvider can generate queries that take up to tens of seconds when querying for increment id.

        $emailFilter = (new Filter())
            ->setField(OrderInterface::CUSTOMER_EMAIL)
            ->setValue($searchTerm . '%')
            ->setConditionType('like');

        $idFilter = (new Filter())
            ->setField(OrderInterface::INCREMENT_ID)
            ->setValue($searchTerm . '%')
            ->setConditionType('like');

        $searchCriteria = $this->searchCriteriaBuilder
            ->addFilters([$emailFilter, $idFilter])
            ->setPageSize(10)
            ->create();

        $orders = $this->orderRepository
            ->getList($searchCriteria)
            ->getItems();

This is an example of a query that is generated by this piece of code:

SELECT `main_table`.*
FROM `sales_order` AS `main_table` 
WHERE ((`customer_email` LIKE 'ST_11000114112') OR (`increment_id` LIKE 'ST_11000114112')) LIMIT 10;

Here's an example of how much time this query takes on our test enviroment (45 seconds):

There's probably more ways to fix this issue but I think it would be a good idea to split the email and the increment_id search. These should not be in the same query with the same value. They're not related that way anyways.

I am probably going to overwrite it for now and look for an '@' in the searchTerm and determine which filter to use based on that and don't set another filter however this would break if you have an '@' in your increment ID for some reason so this should be fixed properly.

[bramstroker]

I'm surprised by the output / resulting query you shared, as it should be customer_email` LIKE 'ST_11000114112%', not customer_email` LIKE 'ST_11000114112' after query is being build by the query builder.

That being said it MySQL should be able to use index without any problem using the construct above as long as the wildcard is in the end and not in the beginning.

So you should investigate if indices in your DB are missing for customer_email and/or increment_id columns.
You can also use EXPLAIN to further investigate.

[dylanmaurits]

Hi @bramstroker,

Thanks for the feedback!

I edited the values from the query and forgot to add the %. However I just changed the query to match the actual functionality and this what it comes down to:

Query:

Result:

Explain:

[bramstroker]

The explain shows it's not using index but using where which means it needs to scan all the rows 2.8 million in the table.

Somehow mysql optimizer is not using right or any index.

You can use this prompt in ChatGPT which should give things to check and possible solutions.

SELECT `main_table`.*
FROM `sales_order` AS `main_table` 
WHERE ((`customer_email` LIKE 'ST_11000114112%') OR (`increment_id` LIKE 'ST_11000114112%')) LIMIT 10;

this query does not use index, but there is an index defined for both customer_email and increment_id fields. what can be the reason.

[abdoredda]

@dylanmaurits

I tried to simulate it in my db and i found the same result, no index used

but if i tried to remove LIMIT it will use the indexes and merge them

i think the problem with the mysql optimizer with limit and OR. It decides to make full table scan instead of use indexes and index merge

after some search i found that if we skip the index merge by not using OR it will prevent this behavior we can use UNION instead like this

the same logic and results but we dont use the OR and make the query more predictable

now each subquery run with its index no need for index merge and will use the indexes and much faster