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:



COUNT(DISTINCT ps.some_other_id)


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

Written By
Query optimization on LIKE
October 04, 2021 12:08PM
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.