N1QL query getting slow

SQL++
1

Hi.
I am using a N1QL query like

select _id from content where pageId = “4a6ac3bd-c0cd-4f8f-9a2a-e5c1e07f91ef” and active=true and
_type=“PageHistory” order by createdAt desc limit 10 offset 10;

and for the above query below is the index created
CREATE INDEX PageHistory ON content(_id,_type,organizationId,userId,pageId,LOWER(name) DESC,DISTINCT tags,createdAt DESC,modifiedAt DESC,active,oldLiquidId) WHERE _type = ‘PageHistory’ WITH { “defer_build”:true };

BUILD INDEX ON content(PageHistory) USING GSI;

its taking 35 seconds to execute ,which is affecting the performance of the application.Is there any other way to write the query so that it will execute in mili seconds?

2

https://blog.couchbase.com/create-right-index-get-right-performance/

CREATE INDEX ix1 ON content(pageId, active, createdAt DESC ,_id ) WHERE _type = ‘PageHistory’ ;

3

After applying the suggested index

select _id from content where pageId = “4a6ac3bd-c0cd-4f8f-9a2a-e5c1e07f91ef” and active=true and
_type=“PageHistory” order by createdAt desc limit 10 offset 10

is working fine and result is coming in 6 ms. but it created problem to the below query.

delete from content where _id in [“4a6ac3bd-c0cd-4f8f-9a2a-e5c1e07f91ef”] and active=true and _type=“PageHistory”;

for delete query it is taking arround 23 sec to execute,which was executing in 5 ms before I applied the index suggested by you. Plz help resolve this issue.

Thank you

4

You can leave your old index or follow as link suggested
CREATE INDEX ix1 ON content(_id, active) WHERE _type = ‘PageHistory’ ;