Why does my query not use the index?

SQL++
,
1

Using 4.1CE

My queries are slow and do not seem to use the GSI indexes what am I doing wrong.

CREATE INDEX idx_pId ON development_bucket(pId);

CREATE INDEX idx_listing ON development_bucket(id, pId, s);

EXPLAIN SELECT doc.id FROM development_bucket doc WHERE doc.id = “list-VyXX$ne3e” OR doc.pId = “list-VyXX$ne3e”;
[
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “development_bucket”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“as”: “doc”,
“keyspace”: “development_bucket”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “(((doc.id) = “list-VyXX$ne3e”) or ((doc.pId) = “list-VyXX$ne3e”))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(doc.id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}
]

2

try this:


CREATE INDEX idx_id ON development_bucket(id);
CREATE INDEX idx_pId_id ON development_bucket(pId,id);

EXPLAIN SELECT doc.id FROM development_bucket doc WHERE doc.id = "list-VyXX$ne3e" 
        UNION ALL
        SELECT doc.id FROM development_bucket doc WHERE doc.pId = "list-VyXX$ne3e";
1 Like
3

Awesome, that fixed it. So I can’t use OR? Can I use OR in 4.5?

4

I think you can,but I am not sure how should OR use index.
may be @geraldss can give us the answer.

5

Use UNION ALL.

You can use OR if both predicates were using the same expression and index. Otherwise, use UNION ALL.

1 Like