CODEWITHSUNDEEP
couchbasesql++
skunwarc
skunwarc

Reputation: 33

How to make CouchBase sub-query with RAW MAX , execution faster

I have this query:

"SELECT meta(d).id as ID, meta(d).cas as CAS, d.* "
  +"FROM (SELECT RAW MAX([t.eventTimestamp, META(t).id])[1] "
      +"FROM `xyz` as t "
      +"WHERE t.`docType` = '" +docType.toString() + "' "
      +predicates
      +"GROUP BY t.id "
      +") as q "
+"JOIN `xyz` as d ON KEYS q"
+"ORDER BY d.eventTimestamp + "ASC " : "DESC "
+"LIMIT $limit OFFSET $offset"

How can I modify the above query, specially this part : d.* to something so that query execution time is faster, without breaking the original query logic. Or is there another way to modify this query for faster execution.

Upvotes: 1

Views: 297

Answers (1)

vsr
vsr

Reputation: 7414

Try use the LIMIT in the subquery.

CREATE INDEX ix1 ON xyz(id, timestamp DESC);

SELECT meta(d).id as ID, meta(d).cas as CAS, d.*
FROM (SELECT RAW m.id
      FROM `xyz` as t
      WHERE t.id is NOT NULL
      GROUP BY t.id
      LETTING m = MAX([t.timestamp, {t.timestamp, META(t).id}])[1]
      ORDER BY m.timestamp DESC LIMIT $limit) as q 
JOIN `xyz` as d ON KEYS q
ORDERBY d.timestamp;

Upvotes: 1

Related Questions