CODEWITHSUNDEEP
couchbasesql++nosql
IgorekPotworek
IgorekPotworek

Reputation: 1335

Couchbase doesn't use index with filter when execute parametized N1QL query

I had created global secondary index with where condition in my Couchbase cluster:

CREATE INDEX commentAuthorSecondaryIndex ON Bucket(author) WHERE (_class = "com.company.Comment") USING GSI;

then I tried execute two similar queries via N1QL (REST API):

curl --header "Content-Type:application/json" http://localhost:8093/query/service -d '
{
  "$class" : "com.company.Comment",
  "statement" : "select count(*) from Bucket WHERE _class = $class and author = $author",
  "$author" : "author455",
  "scan_consistency" : "statement_plus"
}'

and :

curl --header "Content-Type:application/json" http://localhost:8093/query/service -d '
{  
   "statement" : "select count(*) from Bucket WHERE _class = \"com.company.Comment\" and author = $author",
   "$author" : "author455",
   "scan_consistency" : "statement_plus"
}

First query was executed fast 4 seconds while second took only 20 miliseconds. When I used explain keyword I realized that in first query Couchbase doesn't use index.

I suppose that Couchbase firstly create query execution plan and only then evaluate query with parameters. So when query plan is created Couchbase doesn't know that index commentAuthorSecondaryIndex can be used.

Is any way to fix this?

Upvotes: 1

Views: 222

Answers (1)

geraldss
geraldss

Reputation: 2445

Two options--remove the filter from the index, or do not make _class a parameter in the your query. See https://forums.couchbase.com/t/whats-the-best-approach-to-prevent-sql-injection-with-n1ql/11636/8

Upvotes: 1

Related Questions