CB 4.5 N1QL Like '%word%' is slow

I don’t have information about possible performance improvements in newer versions, but generally speaking (not couch queries specifically), using a wildcard at the beginning is going to be relatively slow. Generally significantly slower than wildcards at the end.

I am trying this method and will see how it performs. Tokenizing and then doing wildcards at the end meet all your requirements with better performance. Only issue i have with this is tokenizing on the fly vs storing tokenized values directly in the index. I am slightly concerned about how this will perform but would be much more concerned about starting wildcard perfromance.

Anyways, here it is (tokenizing on whitespace, lowercasing for case insensitivity):

WHERE ANY t IN SPLIT(LOWER(`firstname`)) SATISFIES t LIKE 'bob%'

This will for example match “Jones Bobby”.

Hope this is helpful even though it doesn’t directly answer the question.

2 Likes