Improve couchbase like query performance

1
votes

I have a N1QL query which takes 20 seconds to complete. Database contains 1.1 million records and it has a GSI for locationCode, type. Below is the query.

SELECT * FROM appdb WHERE locationCode="1-1-03-010" AND type="institute" AND lowerName LIKE "%acg%" LILMIT 10 OFFSET 0;

But the performance is very poor. Can you please suggest me on how to improve the performance of the above query? Any help is greatly appreciated.

1
databasenosqlcouchbasen1ql
I assume that's a typo with "LILMIT"? - Matthew Groves

1 Answers

0
votes

Check the Plan Text of the query to see which parts of the query plan are taking the longest. If you are using Couchbase 5 Enterprise, you should be able to look at a graphic representation as well.

You may not be hitting the index you expect, or maybe since you are selecting *, it's taking a long time to actually project the data (I don't know how big your documents are). It's really difficult to speculate without those details.

But if I had to speculate, the LIKE is most likely what is taking up a lot of time. Is this supposed to be a text-driven search? If so, you may want to consider using Couchbase's Full Text Search (FTS) feature instead of N1QL for that use case. Create an FTS index on the lowerName field (at least).