N1QL: performance and advanced features


#1

Hello everybody,

I’m running Couchbase server 4.0 community edition on two different nodes. Each nodes has 256MB of Index RAM and I use N1QL to query a bucket (which has 300MB RAM size) with 600000. I have created an index on a field name type with this command:

CREATE INDEX document_type ON bucket-test(type) USING GSI

and I have excecuted a simple query like this one:

select name from bucket-test where name like “%foo%” and type=“bar”;

but I got the documents very slowly (about 55s to get the result).

Have I done somenthing wrong in the configuration step or in the query? Is there any way to speed all up?

I would also like to know if there are some kind of advanced query function like the levenshtein distance or something similar to create some kind of ranking in the output result.

Couchbase is running on a 2 cpus 4 GB of RAM computer.

Thanks a lot

Alberto


#2

hi @albertobasso88,
can you try 4.1 or later version, and create covering index. That should improve the performance, as it avoids fetching the documents from data-service (check EXPLAIN output).

CREATE INDEX document_type_name ON bucket-test(type, name) USING GSI

-Prasad


#3

Hi @prasad,
thank you for your reply. The performance has gotten better but they are still slow (13s instead of 55s). I believe that it could be fault of the the like operator. Perhaps I’m using too much % symbols?

Alberto


#4

yes, % does impact the perf. If possible, remove the leading wildcard, or rewrite it as a range predicate (for ex: using BETWEEN, and/or less than, greater than comparisons).

If you must use LIKE ‘%foo’, then we have an feature improvement coming up in 4.5.1 release. See MB-20067: Add SUFFIXES() function to enhance pattern matching