MySQL Forums
Forum List  »  Optimizer & Parser

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Query optimization on LIKE
114
October 04, 2021 12:08PM
68
October 04, 2021 01:08PM


Sorry, only registered users may post in this forum.

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.