CODEWITHSUNDEEP
indexingnosqlcouchbasesql++
Thôrek
Thôrek

Reputation: 43

CouchBase: is it possible to speed up a query that does not have a condition?

I am trying to speed up my performances on CouchBase by creating indexes. One of my queries is like this: 

SELECT p.arcid
FROM `flightplans` as p
WHERE ANY route_correct_status IN (
    SELECT DISTINCT arcid,
    ARRAY point.id FOR point IN route.point
    WHEN point.status = "correct-status" END AS list_points_correct_status
    FROM `flightplans`
) SATISFIES route_correct_status.arcid = p.arcid
AND route_correct_status.list_points_correct_status[0] = p.somepoint END

I tried extracting the internal SELECT to find appropriate indexes then I realised there is no condition in it. Everything I tried to create an index for that failed. However it is too slow for me with the primary index.

Is there a way to speed up the construction of an array for each element in my bucket ? In general, can indexes (or some other mechanic) help with queries that do not have a condition ? (by precalculating it maybe ?)

Thanks

Upvotes: 1

Views: 59

Answers (1)

vsr
vsr

Reputation: 7414

Use ANSI Join query. https://blog.couchbase.com/ansi-join-support-n1ql/

CREATE INDEX ix1 ON flightplans( DISTINCT ARRAY point.status FOR point IN route.point END);
CREATE INDEX ix2 ON flightplans(arcid, somepoint);

SELECT p.arcid
   FROM   (SELECT DISTINCT cs.arcid,
            FIRST point.id FOR point IN cs.route.point WHEN point.status = "correct-status" END AS pointid
       FROM `flightplans` AS cs
       WHERE ANY point IN cs.route.point SATISFIES point.status = "correct-status" END) AS d
JOIN `flightplans` AS p ON d.arcid = p.arcid AND d.pointid = p.somepoint;

If you are looking always "correct-status" you can use following both selects must have used covered index

CREATE INDEX ix1 ON flightplans( FIRST point.id FOR point IN route.point WHEN point.status = "correct-status" END, arcid);
CREATE INDEX ix2 ON flightplans(arcid, somepoint);

SELECT p.arcid
    FROM  (SELECT DISTINCT cs.arcid,
            FIRST point.id FOR point IN cs.route.point WHEN point.status = "correct-status" END AS pointid
       FROM `flightplans` AS cs
       WHERE (FIRST point.id FOR point IN cs.route.point WHEN point.status = "correct-status" END) IS NOT NULL) AS d
JOIN `flightplans` AS p ON d.arcid = p.arcid AND d.pointid = p.somepoint;

Upvotes: 1

Related Questions