Hello, I am trying to understand why the following n1ql query works 10x times slower than its subqueries individually.
CREATE INDEX `ix_id_store_type` ON `maindb`(`id`,`store_type`)
SELECT maindb.*
FROM maindb
LET cid = (
SELECT RAW m.contract_id
FROM maindb AS m
WHERE m.store_type = 'hotel'
AND m.id = 330
)[0]
WHERE maindb.store_type = 'contract'
AND maindb.id == cid
I suppose that secondary index ix_id_store_type is not used while executing subquery (by the way i’m not sure that EXPLAIN shows index usage in subquery properly) and I have no idea why.
Below is an example of query plan execution time with “PLAN”.
When you do your query in the workbench you can see the times, % of time , if it uses an index or not.
At present EXPLAIN only includes subquery plan if the subquery present in the FROM clause.
If subquery is present in other parts of query EXPLAIN doesn’t include subquery EXPLAIN (MB-21936 .
If the subquery is not correlated you can do standalone on EXPLAIN on subquery to check plan and same plan will be used by parent query.
Try the following index.
CREATE INDEX ix_store_type_id ON maindb(store_type,id);
The parent query predicate maindb.id == cid , as cid is dynamic the query needs to scan all ids, fetch documents and apply predicate. You can enable profile=timings and check timings and In/Out records for each operator and tune accordingly.
You can also try as 2 separate queries. i.e. execute subquery and pass the result as query parameter to second query. Based on your query this will perform much better.
