If Index contains where clause and query where clause that match index condition needs to be static not parameterized.
If Parameter passing is array and it right side of IN clause( x1 IN $param1), the query needs to retrieve all values of x1 before applying predicate. To make it faster generate query text by replacing parameter with actual values and do adhoc query.
Hi,
Is this issue resolved on Couchbase Enterprise 5.5.3 / Java SDK 2.7.0?
I have the same problem this days. Then I use parameterized N1QL from Java SDK, he ignores my covered indexes, but works then I use simple N1QL.
My problem is the first scenario @vsr1 mentions: “If Index contains where clause and query where clause that match index condition needs to be static not parameterized.”
example:
My coreverd index: CREATE INDEX idx_emails on CONTENT(LOWER(email)) where provider = “Google” and country=“US”
Wrong index used: CREATE INDEX idx_provider on CONTENT(country, provider)
Query:
Select * from CONTENT where LOWER(email) = $1 and provider = $2 and country = $3
params: $1 ="random@gmail.com", $2=“Google”, $3=“US”
Query plan generated during the prepare time and parameters are only available during the execution.
For Partial index selection Index WHERE clause part of the query must be static (no query parameters).
Example: If query during prepare time it uses idx_emails and execution If you pass $3 = “France”, index will not have entries for “France” and returns wrong results. Due to that reason idx_emails will not qualify. That is why idx_provider is used.
You have the following options:
Change portion of query that matches index where clause such that it uses static values (no parameters)
Don’t use prepared statements i.e. set adhoc=true
Don’t create partial index
CREATE INDEX idx_provider on CONTENT(country, provider, LOWER(email) );