Query optimization on LIKE
Posted by:
Sing Lau
Date: October 04, 2021 12:08PM
We have a simple query like this in mysql:
SELECT
ps.productType,
COUNT(DISTINCT ps.some_other_id)
FROM
product_sum ps
WHERE ps.customerId = 'abc'
AND ps.partitionId = 'bbc'
AND ps.orderId LIKE "%123456%"
GROUP BY ps.productType;
orderId field could be one id or multiple ids concat in this format: 123456,234567,345678
We can't remove the leading % in the LIKE condition since the orderId could have multiple ids in the string. Mysql is not using indexing on orderId column since it has NULL value. Tried the FullText index but it cannot be used in partitioned table and also not able to combine with other indexes. Is there any other way it can be optimized? Please advise. Thank you.
Subject
Views
Written By
Posted
Query optimization on LIKE
722
October 04, 2021 12:08PM
419
October 04, 2021 01:08PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.