时间戳

首页 > 解决方案 > CouchBase：是否可以加快没有条件的查询？

indexing - CouchBase：是否可以加快没有条件的查询？

问题描述

我试图通过创建索引来加快我在 CouchBase 上的表现。我的一个查询是这样的：

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

我尝试提取内部 SELECT 以找到适当的索引，然后我意识到其中没有条件。我试图为此创建索引的一切都失败了。但是，主索引对我来说太慢了。

有没有办法加快为我的存储桶中的每个元素构建一个数组？一般来说，索引（或其他一些机制）可以帮助没有条件的查询吗？（通过预先计算它可能吗？）

谢谢

标签： indexingnosqlcouchbasen1ql

解决方案

使用 ANSI Join 查询。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;

如果您一直在寻找“正确状态”，则可以使用以下两个选择必须使用覆盖索引

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;

推荐阅读