Which form of this query is optimal?

N1QL
1

These queries return the same result

    WHERE  ANY v IN  [`data`.`to`,`data`.`from`] SATISFIES v = "accountname1" END
    AND ANY v IN  [`data`.`to`,`data`.`from`] SATISFIES v = "accountname2" END
    AND type="action" 
    SELECT  *
    ORDER BY block.num DESC
    LIMIT 10

.

    WHERE type="action" 
    AND data.`from` = "accountname1"
    OR data.`from` = "accountname2"
    AND data.`to` = "accountname2"
    OR data.`to` = "accountname1"
    SELECT  *
    ORDER BY block.num DESC
    LIMIT 10

Currently I have a basic index on data.from and data.to which does help to speed up the first query, however both queries are much slower than they should be since they are using using union and index scan in Plan. I tried adding additional indexes however I was not able to construct them in a way that these queries benefited.

So which one of these queries is the most optimal and what would be the most efficient index?

2

Are you sure both queries same? First query looking if one from accountname1, to accountname2 vice versa,

Second depends on precedence.

  CREATE INDEX ix1 ON default(`data`.`to`,`data`.`from`,  block.num DESC ) WHERE type = "action";
  SELECT * 
    FROM default USE KEYS (
            SELECT  RAW META().id
            FROM default
            WHERE  type = "action" 
                 AND  ((`data`.`to` = "accountnam1"  AND  data.`from` = "accountname2")
                        OR (`data`.`to` = "accountnam2"  AND  data.`from` = "accountname1")
             ORDER BY block.num DESC
             LIMIT 10);
1 Like
3

Yes your query is more elegant thanks!