select * from AAAAAA where _class=‘com.xxxx.yyyy.zzzz’ AND
effectiveDateTime <= STR_TO_MILLIS(now_str()) AND ANY store IN entity.stores SATISFIES store = ‘GB:SSSSS’ END AND ANY gtin IN entity.condition.productGtins SATISFIES gtin = ‘111111111111111’ END
Supporting index created is
CREATE INDEX xxxxx_yyyyy_Index ON AAAAAA(effectiveDateTime,entity.stores,entity.condition.productGtins)
WHERE _class =“com.xxxx.yyyy.zzzz”
using GSI with { “num_replica”:1 };
productGtins are quite deep inside the document(root->entity->condition->productGtins).
During load test this query is taking lot of time for execution.
Is the index creation correct??
If index creation is correct where we would have gone wrong?
Thanks for the info. But unfortunately it didn’t help. Have tried with 2 seperate indexes and tested independently with out load. Still the query takes more than 1.5 seconds with only 30k documents in the bucket.
Here are the updated indexes,
Compund Array Index with productGtin this didn’t work:
CREATE INDEX xxxxx_yyyyy_Index ON AAAAAA(effectiveDateTime,entity.stores,DISTINCT ARRAY gtin.productGtins FOR gtin IN entity.condition END)
WHERE _class =“com.xxxx.yyyy.zzzz”
using GSI with { “num_replica”:1 };
Droped the above index and created below index which also didn’t help:
Compund Array Index with stores
CREATE INDEX xxxxx_yyyyy_Index ON AAAAAA(effectiveDateTime,DISTINCT ARRAY str.stores FOR str IN entity END,entity.condition.productGtins)
WHERE _class =“com.xxxx.yyyy.zzzz”
using GSI with { “num_replica”:1 };
Can you confirm if index creation is proper or any modifications are required??
CREATE INDEX ix1 ON default(effectiveDateTime,
DISTINCT ARRAY s FOR s IN entity.stores END,
entity.condition.productGtins)
WHERE _class = "com.xxxx.yyyy.zzzz";
CREATE INDEX ix2 ON default(effectiveDateTime,
DISTINCT ARRAY g FOR g IN entity.condition.productGtins END,
entity.stores)
WHERE _class = "com.xxxx.yyyy.zzzz";
SELECT d1.* FROM (SELECT RAW META(s).id
FROM default AS s USE INDEX (ix1)
WHERE s._class="com.xxxx.yyyy.zzzz"
AND s.effectiveDateTime <= NOW_MILLIS()
AND ANY s IN s.entity.stores SATISFIES s = "GB:SSSSS" END
AND ANY gtin IN s.entity.condition.productGtins SATISFIES gtin = "111111111111111" END) AS d
JOIN default AS d1 ON KEYS d;
Replace ix1 and ix2 and see which performs better and use that index.
Also try this. Do EXPLAIN on subquery and make sure it is covered.
CREATE INDEX ix1 ON default(effectiveDateTime,
DISTINCT ARRAY s FOR s IN entity.stores END)
WHERE _class = "com.xxxx.yyyy.zzzz";
CREATE INDEX ix2 ON default(effectiveDateTime,
DISTINCT ARRAY g FOR g IN entity.condition.productGtins END)
WHERE _class = "com.xxxx.yyyy.zzzz";
SELECT d1.* FROM (SELECT META(s).id
FROM default AS s
WHERE s._class="com.xxxx.yyyy.zzzz"
AND s.effectiveDateTime <= NOW_MILLIS()
AND ANY s IN s.entity.stores SATISFIES s = "GB:SSSSS" END
INTERSECT
SELECT META(s).id
FROM default AS s
WHERE s._class="com.xxxx.yyyy.zzzz"
AND s.effectiveDateTime <= NOW_MILLIS()
AND ANY gtin IN s.entity.condition.productGtins SATISFIES gtin = "111111111111111" END) AS d
JOIN default AS d1 ON KEYS d.id;