Sorry for asking the same questions but my head hurts from trying to find a solution.
My request here take more than 5 seconds to retrive 10 results when I add Order By clause. I tried to create compound indexes as idx_origin_class_metadata_name (origin, _class, metadata.name) or class_origin_metadata_name (_class, orgin, metadata.name), but nothing change.
SELECT t.metadata.name, META(t).id, ARRAY_STAR((ARRAY_STAR(p).metadata)).name AS breadcrumb
FROM `bucket-data` AS t
NEST `bucket-data` AS p
ON KEYS t.ancestorIds
WHERE t._class = "mypackage.Category"
AND ( t.origin = "GLOBAL" AND t.metadata.configurations.GLOBAL.enabled = TRUE )
AND ARRAY_COUNT(t.productIds.GLOBAL) > 100
ORDER BY t.metadata.name ASC
LIMIT 10 OFFSET 0
The ancestorIds hold other categories IDs. My goal is to generate data with my current category and the names of its ancestors.
I don’t what’s going wrong!
Please help and thank you
If you need ORDER it needs to produce all qualified items and sort (even though you have LIMIT, OFFSET). Query engine can make certain optimizations based on index (If Query ORDER BY matches index order, avoid sort).
CREATE INDEX ix1 ON `bucket-data`(origin, metadata.configurations.GLOBAL.enabled,
metadata.name, ARRAY_COUNT(productIds.GLOBAL), ancestorIds)
WHERE _class = "mypackage.Category";
SELECT t.metadata.name, META(t).id, ARRAY_STAR((ARRAY_STAR(p).metadata)).name AS breadcrumb
FROM `bucket-data` AS t
NEST `bucket-data` AS p
ON KEYS t.ancestorIds
WHERE t._class = "mypackage.Category"
AND ( t.origin = "GLOBAL" AND t.metadata.configurations.GLOBAL.enabled = TRUE )
AND ARRAY_COUNT(t.productIds.GLOBAL) > 100
AND t.metadata.name IS NOT NULL
ORDER BY t.metadata.name ASC
LIMIT 10 OFFSET 0;
OR
SELECT t.metadata.name, META(t).id,
(SELECT RAW p.metadata.name FROM `bucket-data` AS p USE KEYS t.ancestorIds) AS breadcrumb
FROM `bucket-data` AS t
WHERE t._class = "mypackage.Category"
AND t.origin = "GLOBAL"
AND t.metadata.configurations.GLOBAL.enabled = TRUE
AND ARRAY_COUNT(t.productIds.GLOBAL) > 100
AND t.metadata.name IS NOT NULL
ORDER BY t.metadata.name ASC
LIMIT 10 OFFSET 0;
Thanks for your answer.
Assuming metadata.configurations and productIds are Map, do I have to create the index for each key?
ARRAY_STAR function works well for Array, is there a function for Map?
You can, Predicate pushdown to indexer on Object may not happen. In query you need to Object key exactly same.
If Object is too big you can reconstruct object ( you need to use reconstructed object in query exactly to cover)
CREATE INDEX ix1 ON `bucket-data`(origin, metadata.configurations,
metadata.name, productIds, ancestorIds)
WHERE _class = "mypackage.Category";
The execution time passed from 8s to 3 seconds.
Knowing that configurations and productIds objects can both have more than 30 keys. Their values often change. I thought it is not a best practice to use whole objects and arrays to build indexes.