N1QL query taking more time to execute

SQL++
1

Hello

I am trying to execute the below query which returns 9 records from around 5 lakhs documents and it is taking around 7 seconds to execute.

SELECT _id,name,url,tags,status,createdAt FROM content where type = “IMAGE” and _type = “UserAsset”
AND createdAt IS NOT MISSING
AND ANY v IN [LOWER(name),LOWER(tags)] SATISFIES v LIKE “%a9e82812%” END

for this query i am using indexing as below
CREATE INDEX ix1c_UserAsset ON content(createdAt DESC,type,_type,status, name,DISTINCT tags,url,_id,categoryName) WHERE _type=“UserAsset”

The query should return result in less than a second but it is taking 7 seconds to execute.Am I missing something?

2

LIKE with leading % takes time. Checkout alternatives https://dzone.com/articles/a-couchbase-index-technique-for-like-predicates-wi

Also checkout https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf how to create right index

3

Thanks for your shared documents.

Now I have modified the query like below

SELECT _id,name,url,tags,status,createdAt FROM content where
ANY array_element IN SUFFIXES(LOWER(tags)) SATISFIES array_element
LIKE ‘bird%’ END
and type = “PUBLICIMAGE”

and the index for the query is like

create index test_UserAsset on content(DISTINCT ARRAY array_element
FOR array_element IN SUFFIXES(LOWER(tags)) END)
where type=“PUBLICIMAGE”

Here tags is a array.for tags I am not getting any results here. Instead of tags other fields like name , status is working fine depending up on like operator variable .I need results by using tags .Is there any other solution or I am doing any mistake here

4

You need to create nested array index.

CREATE INDEX  test_UserAsset ON content(DISTINCT ARRAY
                           (DISTINCT ARRAY ae FOR ae IN SUFFIXES(LOWER(tag)) END) 
                           FOR tag IN tags END)
WHERE type="PUBLICIMAGE";

SELECT _id,name,url,tags,status,createdAt 
FROM content 
WHERE  ANY tag IN  tags SATISFIES ( ANY ae IN SUFFIXES(LOWER(tag)) SATISFIES ae
LIKE "bird%" END) END
AND type = "PUBLICIMAGE";
5

While cretaing Index mentioned by you I am getting syntax error.

“code”: 3000,
“msg”: “syntax error - at FOR”,

6 
CREATE INDEX  test_UserAsset ON content(DISTINCT ARRAY
                           (DISTINCT ARRAY ae FOR ae IN SUFFIXES(LOWER(tag)) END) 
                           FOR tag IN tags END)
WHERE type="PUBLICIMAGE";
1 Like
7

Thanks
Now its working fine.Response is coming in milliseconds, for record count 1711.