Help for optimizing

Couchbase doesn’t have transaction. Its atomicity is document. It doesn’t fetch all documents then update.
it fetches documents streams and then update will happen.
When CAS mismatched documents it return error and continues.

UPDATE is done with WHERE clause , You could also UPDATE in batches with LIMIT and repeat until no more mutations.

example:
UPDATE default d SET d.c1 = “x1”, d.c2 =“x3”
WHERE (d.c1 != “x1” OR d.c2 !=“x3”) AND d.type = “doc” …
LIMIT 1000;
Above statement avoids unnecessary mutation (d.c1 != “x1” OR d.c2 !=“x3”).
If you create index that covers predicate by repeating statement you can avoid repeatable update.

1 Like

When CAS mismatched documents it return error and continues.

Can you explain it more?

When ever document fetched from data node it retrieves CAS during update same value passed to data node. If the value is mismatched data node returns error and same error will be passed to application.

@vsr1 can you tell me what is the best index for the following query?

SELECT order.id, order.title, category.image FROM default order
LEFT JOIN default category ON KEYS order.categoryKey
        WHERE order.type='order' AND order.userKey=$userKey AND order.method!=$method
        ORDER BY order.paidDate DESC,order.createDate DESC

I have an index but explain show me it’s not covering

CREATE INDEX ix1 ON default( userKey, paidDate DESC, createDate DESC, method, categoryKey, id, title )
WHERE type=‘order’;

@vsr1 I know that I asked it before , but I really cannot understand why I must add paidDate DESC, createDate DESC after first single equality key

ORDER BY must follow index key order. It is okay to skipping single equality key.

Can you tell me when ix2 is better than ix1

CREATE INDEX ix1 ON default( userKey, paidDate DESC, createDate DESC, method, categoryKey, id, title )
WHERE type=‘order’;

CREATE INDEX ix2 ON default( userKey, method, paidDate DESC, createDate DESC, categoryKey, id, title )
WHERE type=‘order’;

What if I have the following query (added AND order.status=$status)

SELECT order.id, order.title, category.image FROM default order
LEFT JOIN default category ON KEYS order.categoryKey
        WHERE order.type='order' AND order.userKey=$userKey AND order.status=$status AND order.method!=$method
        ORDER BY order.paidDate DESC,order.createDate DESC
CREATE INDEX ix3 ON default( userKey, paidDate DESC, createDate DESC,status, method, categoryKey, id, title )
WHERE type=‘order’;

CREATE INDEX ix4 ON default( userKey,status, method, paidDate DESC, createDate DESC, categoryKey, id, title )
WHERE type=‘order’;

It all depends on your data? How many rows qualifies etc. You can try different indexes and see which one performs better.

order.method !=$method you are eliminating one of the many values, i thought order is expensive so ix1 is better. If used ix2 query must do explicit sort and can’t leverage index order.
With ix1 if you add the following additional predicate it might be perform better.

SELECT order.id, order.title, category.image FROM default order
LEFT JOIN default category ON KEYS order.categoryKey
        WHERE order.type='order' AND order.userKey=$userKey AND order.method!=$method
        ABD order.paidDate IS NOT NULL  AND order.createDate  IS NOT NULL
        ORDER BY order.paidDate DESC,order.createDate DESC

The following might be better.

CREATE INDEX ix5 ON default( userKey, status, paidDate DESC, createDate DESC, method, categoryKey, id, title )
WHERE type=‘order’;

1 Like