Unsing array indexing what's wrong?

SQL++
1

the content i’m looking for in 400000 items is :
“xmldata”: [
{
“controlaccess”: [
…
],
“did”: [
{
“unitdate”: [
{
“xmlattrs”: {
“label”: “Date de l’unité documentaire”,
“normal”: “1601/1700”
},
“xmlvalue”: “XVIIe siècle”
}
],
“unitid”: [
{
“xmlattrs”: {
“label”: “Cotes extrêmes”
},
“xmlvalue”: “2 Fi 9”
}
],
“unittitle”: [
…
]
}
]
}
]

this query
select * from Ead where ANY v IN xmldata[0].did SATISFIES ANY y IN v.unitid SATISFIES y.xmlvalue LIKE “%2 Fi%” END END
find results within 35 sec using PrimaryScan.
I create an array index
CREATE INDEX Ead_unitid ON Ead((distinct (array (distinct (array y for y in (v.unitid) end)) for v in ((xmldata[0]).did) end)))
The same query still using PrimaryScan
What i’m doing wrong ?

2

For one thing, “%2 Fi%” may not use an index. Can you try a different condition here, e.g. “2 Fi%”, or an equality condition?

3

I try LIKE “2 Fi%” and = “2 Fi 9"
still the same primaryscan
"plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “Ad01DiffusionEad_primary”,
“keyspace”: “Ad01DiffusionEad”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “Ad01DiffusionEad”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “any v in (((Ad01DiffusionEad.xmldata)[0]).did) satisfies any y in (v.unitid) satisfies ((y.xmlvalue) = “2 Fi 9”) end end”
},

4

Ok, this should work.

CREATE INDEX Ead_unitid ON default
(DISTINCT ARRAY (DISTINCT ARRAY y.xmlvalue FOR y IN v.unitid END) FOR v IN xmldata[0].did END);
1 Like