I am disappointed with query performance: (Community Edition 6.5.1 build 6299)
it taking 2.1 seconds to find less than 4500 docs from a bucket, see the output below.
Indexing working properly:
Index:
CREATE INDEX account_head ON master_erp(cid,status) WHERE (type = “account_head”)
Whats wrong? how can i improve the performance?
Now I am worried because day by day my bucket will increase…so how can i fix this issue?
If i cant fix it then i must switch back to mysql…
In CE query service uses 4 cores only.
Your query requires ORDER BY which requires produce all possible values (even Fetch because it is not covered) and perform sort.
Experiment without ORDER BY.
CREATE INDEX ix1 ON (cid, serial DESC, status) WHERE type = “account_head”;
This can avoid sort but with extra over head on indexer because it needs to scan serial, status. But may worth it to reduce fetch.
You removed LIMIT too. The size of results increased by 360 times (3650).
@mcsj120 @vsr1 no its even worse…
how should i resolve this?
if ORDER BY and LIMIT effect query performance then how we paginate in our webapp?
Please checkout earlier post #4
@vsr1
Actually my final query was …
SELECT
a.aid
,a.name
,a.description
,a.ledger
,a.balance
,a.baltype
,a.status
,a.create_date
,g.name as group_name
,g.group_type
FROM master_erp a
LEFT JOIN master_erp g ON a.account_group=META(g).id AND g.type=“account_group”
WHERE a.type=“account_head” AND a.status IN[0,1] AND a.cid=“company::2” AND REGEXP_CONTAINS(a.ledger, “91001145”) ORDER BY a.serial DESC LIMIT 10 OFFSET 0;
how can I make index with REGEXP_CONTAINS ???
REGEXP_CONTAINS(a.ledger, “91001145”) has dynamic value. Those will applied in side query. you will not able to define in the index key unless all the queries has 91001145
@vsr1 what about LIKE “Cash %” ?? this seems similar to regexp_contains right??
Prefix LIKE is different vs regexp_contains.
@vsr1 so if my query contains regexp_contains then no way to indexing right? couchbase fail here?
Please note nothing will fail. The values will not push to indexer. It will use other fields to get the document sand apply filter later. ie regexp_contains is treated as post index scan filter.
what do you mean by this ?
CREATE INDEX ix1 ON master_erp (cid, serial DESC, status) WHERE type = “account_head”;
SELECT * FROM master_erp
WHERE a.type=“account_head” AND a.status = 1 AND a.cid=“company::2” AND REGEXP_CONTAINS(a.ledger, “91001145”) ORDER BY a.serial DESC LIMIT 10 OFFSET 0;
Do EXPALIN and look IndexScan section.
IndexScan uses cid=“company::2”, status = 1 and produces all document keys
Then Fetches documents
Then apply the Filters including REGEXP_CONTAINS Further eliminates
Check out Understanding Index Scans in Couchbase N1QL Query in https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf
@vsr1 thank you very much for your patience and support. I appreciate your help.
