Search code examples
sql++

Below query is taking 2 mins ,can it be tuned more

Help me tune below query: SELECT META(m).id, m.sch_name AS name FROM pricing_qa_1 AS m UNNEST m.pri_sch_ref AS r WHERE m.dtype = "mprisch" AND r IN( SELECT RAW META(a).id FROM pricing_qa_1 AS a UNNEST a.prod_prof AS p WHERE a.dtype = "prisch" AND (p.prod_cd = "WMOBAC000A1") ).

Solution

  • Use Array indexing with ANSI JOIN described here https://blog.couchbase.com/ansi-join-support-n1ql/ (Example 12, 13, 14)

    CREATE INDEX ix1 ON pricing_qa_1 ( DISTINCT v.prod_cd FOR v IN prod_prof END ) WHEERE dtype = "prisch";
CREATE INDEX ix2 ON pricing_qa_1 ( DISTINCT pri_sch_ref ) WHEERE dtype = "mprisch";

SELECT META(m).id , m.sch_name AS name
FROM pricing_qa_1 AS p
JOIN pricing_qa_1 AS m
ON ANY mv IN m.pri_sch_ref SATISFIES mv = META(p).id END AND m.dtype = "mprisch"
WHERE p.dtype = "prisch" AND ANY v IN p.prod_prof SATISFIES v.prod_cd = "WMOBAC000A1" END ;