HI,
I know this question as alredy been posted but I cannot find a good answer.
I have this query
SELECT * FROM store WHERE _type=“Product” ORDER BY updated LIMIT 30
which take 4.8s with 70k products.
If I remove the order by clause the time is ~9ms
I have this indexes:
CREATE INDEX ALL__type ON store(_type)
CREATE INDEX ALL__updated ON store(updated)
CREATE INDEX ALL__updated2 ON store(_type,updated)
and this is the EXPLAIN result
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “ALL__type”,
“index_id”: “1cefee2f50af8100”,
“keyspace”: “store”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“Product”"
],
“Inclusion”: 3,
“Low”: [
"“Product”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“keyspace”: “store”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((store._type) = “Product”)”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
}
]
}
}
]
},
{
"#operator": “Order”,
“limit”: “30”,
“sort_terms”: [
{
“expr”: “(store.updated)”
}
]
},
{
"#operator": “Limit”,
“expr”: “30”
},
{
"#operator": “FinalProject”
}
]
},
“text”: “SELECT * FROM store WHERE _type=“Product” ORDER BY updated LIMIT 30”
}
]
How can I improve the performance of this query with the order by? Have I created the right indexes?