N1ql query taking more time to fetch result


#1

Hi,

Below query is taking more than one minute to give result.IS there any way we can tune. I have one index on dtype. Please suggest if need to create any other index.
SELECT a.*, META(a).id FROM pricing_qa_2 AS a WHERE a.dtype = “qqcfxspd” AND 1 = 1
ORDER BY LOWER(sch_name) ASC, aud_info.ts DESC OFFSET 0 limit 24


#2

The following Index and query combinations should have used Index Order avoided sort and extra fetch due to sort. Checkout EXPLAIN.

CREATE INDEX ix1 ON pricing_qa_2(LOWER(sch_name), aud_info.ts DESC) WHERE dtype = "qqcfxspd";

SELECT a.*, META(a).id 
FROM  `pricing_qa_2`  AS a 
WHERE a.dtype = "qqcfxspd" AND LOWER(sch_name) IS NOT NULL
ORDER BY LOWER(sch_name) ASC, aud_info.ts DESC 
OFFSET 0 
LIMIT 24;

OR

CREATE INDEX ix1 ON pricing_qa_2(dtype, LOWER(sch_name), aud_info.ts DESC);

SELECT a.*, META(a).id 
FROM  `pricing_qa_2`  AS a 
WHERE a.dtype = "qqcfxspd" 
ORDER BY LOWER(sch_name) ASC, aud_info.ts DESC 
OFFSET 0 
LIMIT 24;