Hello,
After adapting my query based on your suggestion and inserting the index based on my schema, I was not really able to understand why my index was not used.
Let me start with the INDEX I’ve created:
CREATE INDEX idx_location ON `default`(RADIANS((location[0]).lat), RADIANS((location[0]).lng));
As it is an array of objects, I’ve indexed the first object lat and lng. Even tried without the array index, but without effects either.
The query I’ve ended up with:
SELECT itemEntry, relatedItemDetails, distance, weight_distance, itemEntry.total_weight FROM (
SELECT itemEntry, relatedItemDetails, distance, weight_distance
FROM default itemEntry
NEST default relatedItemDetails ON KEYS (itemEntry.related_id)
LET distance = CASE
WHEN (RADIANS(relatedItemDetails[0].location[0].lat) >= -0.0078393251908876
and RADIANS(relatedItemDetails[0].location[0].lat) <= 0.0078393251908876)
and (RADIANS(relatedItemDetails[0].location[0].lng) >= -0.0078393251908876
and RADIANS(relatedItemDetails[0].location[0].lng) <= 0.0078393251908876)
OR acos(sin(0) * sin (relatedItemDetails[0].location[0].lat) + cos(0) * cos(relatedItemDetails[0].location[0].lat) * cos (relatedItemDetails[0].location[0].lng - 0)) <= 0.0078480615288024
THEN (acos(sin( 0 )*sin(radians(relatedItemDetails[0].location[0].lat)) + cos( 0 )*cos(radians(relatedItemDetails[0].location[0].lat))*cos(radians(relatedItemDetails[0].location[0].lng)-( 0 ))) * 6371)
ELSE null
END,
weight_distance = CASE
WHEN (RADIANS(relatedItemDetails[0].location[0].lat) >= -0.0078393251908876
and RADIANS(relatedItemDetails[0].location[0].lat) <= 0.0078393251908876)
and (RADIANS(relatedItemDetails[0].location[0].lng) >= -0.0078393251908876
and RADIANS(relatedItemDetails[0].location[0].lng) <= 0.0078393251908876)
OR acos(sin(0) * sin (relatedItemDetails[0].location[0].lat) + cos(0) * cos(relatedItemDetails[0].location[0].lat) * cos (relatedItemDetails[0].location[0].lng - 0)) <= 0.0078480615288024
THEN ((acos(sin( 0 )*sin(radians(relatedItemDetails[0].location[0].lat)) + cos( 0 )*cos(radians(relatedItemDetails[0].location[0].lat))*cos(radians(relatedItemDetails[0].location[0].lng)-( 0 ))) * 6371) / 100) - (acos(sin( \$lat )*sin(radians(relatedItemDetails[0].location[0].lat)) + cos( 0 )*cos(radians(relatedItemDetails[0].location[0].lat))*cos(radians(relatedItemDetails[0].location[0].lng)-( 0 ))) * 6371)
ELSE 0
END
WHERE itemEntry._type = "feed" AND
itemEntry.account_id = "user_account::5"
) feedEntry
WHERE (feedEntry.distance <= 100 OR feedEntry.distance IS NULL)
ORDER BY (feedEntry.total_weight + feedEntry.weight_distance), itemEntry.created_at DESC
The EXPLAIN of the query above:
{
"requestID": "be8b374d-9c18-4d94-8854-80e09445a2d2",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "idx_type_accountid",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"feed\"",
"\"user_account::5\""
],
"Inclusion": 3,
"Low": [
"\"feed\"",
"\"user_account::5\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "itemEntry",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Nest",
"as": "relatedItemDetails",
"keyspace": "default",
"namespace": "default",
"on_keys": "(`itemEntry`.`related_id`)"
},
{
"#operator": "Let",
"bindings": [
{
"expr": "case when (((((-0.0078393251908876) \u003c= radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`))) and (radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)) \u003c= 0.0078393251908876)) and (((-0.0078393251908876) \u003c= radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`))) and (radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`)) \u003c= 0.0078393251908876))) or (acos(((sin(0) * sin(((((`relatedItemDetails`[0]).`location`)[0]).`lat`))) + ((cos(0) * cos(((((`relatedItemDetails`[0]).`location`)[0]).`lat`))) * cos((((((`relatedItemDetails`[0]).`location`)[0]).`lng`) - 0))))) \u003c= 0.0078480615288024)) then (acos(((sin(0) * sin(radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)))) + ((cos(0) * cos(radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)))) * cos((radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`)) - 0))))) * 6371) else null end",
"variable": "distance"
},
{
"expr": "case when (((((-0.0078393251908876) \u003c= radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`))) and (radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)) \u003c= 0.0078393251908876)) and (((-0.0078393251908876) \u003c= radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`))) and (radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`)) \u003c= 0.0078393251908876))) or (acos(((sin(0) * sin(((((`relatedItemDetails`[0]).`location`)[0]).`lat`))) + ((cos(0) * cos(((((`relatedItemDetails`[0]).`location`)[0]).`lat`))) * cos((((((`relatedItemDetails`[0]).`location`)[0]).`lng`) - 0))))) \u003c= 0.0078480615288024)) then (((acos(((sin(0) * sin(radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)))) + ((cos(0) * cos(radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)))) * cos((radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`)) - 0))))) * 6371) / 100) - (acos(((sin($lat) * sin(radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)))) + ((cos(0) * cos(radians(((((`relatedItemDetails`[0]).`location`)[0]).`lat`)))) * cos((radians(((((`relatedItemDetails`[0]).`location`)[0]).`lng`)) - 0))))) * 6371)) else 0 end",
"variable": "weight_distance"
}
]
},
{
"#operator": "Filter",
"condition": "(((`itemEntry`.`_type`) = \"feed\") and ((`itemEntry`.`account_id`) = \"user_account::5\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "`itemEntry`"
},
{
"expr": "`relatedItemDetails`"
},
{
"expr": "`distance`"
},
{
"expr": "`weight_distance`"
}
]
}
]
}
}
]
},
{
"#operator": "Alias",
"as": "feedEntry"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`feedEntry`.`distance`) \u003c= 100) or ((`feedEntry`.`distance`) is null))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`feedEntry`.`itemEntry`)"
},
{
"expr": "(`feedEntry`.`relatedItemDetails`)"
},
{
"expr": "(`feedEntry`.`distance`)"
},
{
"expr": "(`feedEntry`.`weight_distance`)"
},
{
"expr": "((`feedEntry`.`itemEntry`).`total_weight`)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"expr": "((`feedEntry`.`total_weight`) + (`feedEntry`.`weight_distance`))"
},
{
"desc": true,
"expr": "((`feedEntry`.`itemEntry`).`created_at`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
],
"status": "success",
"metrics": {
"elapsedTime": "51.850782ms",
"executionTime": "51.782029ms",
"resultCount": 1,
"resultSize": 9503
}
}
I don’t really understand why the INDEX is not being applied. What am I missing here?
Thanks!