Speed up array index and query

SQL++
,
1

I have an index for some tickets:

CREATE INDEX flat_ticket ON bucket2(channels[0], doctype, isPaid, isOpen, isDeleted, isVoid, isComped, guestCount, createdAt, subtotal, totalWithFee, tax, tipAmount, tipPercent, payments,
ARRAY [i.category, i.department, i.name, i.quantity, i.voided, i.wasSent, i.isDeleted, i.comped, i.currentPrice, 
(ARRAY [m.isDeleted, m.voided, m.wasSent, m.price, m.quantity, m.name] FOR m IN i.mods END)
] FOR i IN items END) WHERE doctype="ticket" AND NOT isDeleted

and my query is:

`SELECT`+
    ` ARRAY [`+
      ` i.category`+
      ` i.department,`+
      ` i.quantity,`+
      ` i.currentPrice,`+
      ` (ARRAY [m.price, m.name] FOR m IN i.mods END)`+
    `] FOR i IN items END`+
  ` FROM ${bucket} ticket`+
  ` USE INDEX (flat_ticket)`+
  ` WHERE channels[0]="${channel}"`+
  ` AND doctype="ticket"`+
  ` AND createdAt > "${start}"`+
  ` AND createdAt < "${end}"`+
  ` AND isPaid`+
  ` AND NOT isDeleted`+
  ` AND NOT isOpen`+
  ` AND NOT isVoid`+
  ` AND NOT isComped`

Currently this take about 20 seconds to execute which is far too slow. Any tips on improving either the index or the query?

2

Please post your explain and the couchbase version you’re running. Thanks.

3

The Index is too wide. Try to create Index with only predicate fields.
If the isPaid, isDeleted, isOpen, isVoid, isComped are boolean values (i.e. true or false) use the following index and query.

CREATE INDEX flat_ticket ON bucket2(isDeleted, isPaid, isOpen, isVoid, isComped, channels[0], createdAt) WHERE doctype = “ticket”;
SELECT … FROM … WHERE channels[0]="${channel}" AND createdAt > $start AND createdAt < $end AND
isPaid = true AND isDeleted = false AND isOpen = false AND isVoid = false AND isComped = false;

4

Wow huge improvement, I’ve been doing indexing all wrong.

My new index looks like:

CREATE INDEX flat_ticket ON bucket2(isDeleted, isPaid, isOpen, isVoid, isComped, channels[0], createdAt) WHERE doctype="ticket"

And now I’m torn between how to structure the query, I’ve only ran a few tests but neither seem to have a clear edge over the other, other than how the data is returned.
Query 1:

SELECT items[*].category, items[*].department, items[*].currentPrice FROM bucket2 WHERE channels[0]="traditionsflat9654_traditionsflat9654" AND doctype="ticket" AND createdAt>"2017-05-14T00:00:00-05:00" AND createdAt<"2017-05-16T00:00:00-05:00" AND isDeleted=false AND isPaid=true AND isOpen=false AND isVoid=false AND isComped=false;

Query 2:

SELECT ARRAY [i.department, i.category, i.currentPrice] FOR i IN items FROM bucket2 WHERE channels[0]="traditionsflat9654_traditionsflat9654" AND doctype="ticket" AND createdAt>"2017-05-14T00:00:00-05:00" AND createdAt<"2017-05-16T00:00:00-05:00" AND isDeleted=false AND isPaid=true AND isOpen=false AND isVoid=false AND isComped=false;

Is there any reason to choose one over the other? Or is there another better way altogether?

5

Either one works fine but each has different representation.
How about the following array of OBJECTS so that it has field-name present.

SELECT ARRAY {i.department, i.category, i.currentPrice} FOR i IN items END  AS items
FROM bucket2 WHERE channels[0]="traditionsflat9654_traditionsflat9654" AND doctype="ticket" AND 
createdAt>"2017-05-14T00:00:00-05:00" AND createdAt<"2017-05-16T00:00:00-05:00" AND 
isDeleted=false AND isPaid=true AND isOpen=false AND isVoid=false AND isComped=false;

See detailed articles at: https://blog.couchbase.com/n1ql-practical-guide/