Optimizing CB list with IN instead of multiple LIKE conditions – Seeking advice

Hi everyone,I’m working on a list in Community Builder using the following filter condition:

The issue is that the list takes a bit of time to load, possibly due to the use of multiple LIKE conditions. To optimize it, I’m considering replacing these LIKE statements with an IN condition, like this:  If the cb_membertype values are consistent and don’t require partial matching, this could potentially speed things up.Alternatively, if partial matches are necessary, I’ve adjusted the LIKE conditions to avoid % at the beginning:



Does anyone have experience with a similar optimization? Do you think using IN instead of multiple LIKE statements is a good approach in Community Builder?Thanks for your insights!

I do not recommend using prefixed wildcard searches (e.g. LIKE '%value%') as they cannot be indexed. Suffixed wildcard searches (e.g. LIKE 'value%') can be indexed safely. The only way to optimize those is with full text searches, which will require some specific indexes to be added to _comprofiler and some different functions used. See the MySQL documentation below for further details.

dev.mysql.com/doc/refman/8.4/en/fulltext-search.html

I'm not sure why you'd need partial matches though and don't recommend doing that. It's always going to be more efficient to be explicit in what information you're requesting so yes the IN operator will be substantially faster.