WHERE clause when USE INDEX

SQL++
, ,
1

Let’s say we have a simple index:

CREATE INDEX `simple_idx` ON `bucket`(`member`) WHERE ((meta().`id`) LIKE "KEY_PREFIX::%")

When I want to query this index, I write:

SELECT *
FROM `bucket` USE INDEX (simple_idx)
WHERE member = "TheOneIWant"

Which doesn’t work.

I have to do:

SELECT *
FROM `bucket` USE INDEX (simple_idx)
WHERE member = "TheOneIWant"
   AND ((meta().`id`) LIKE "KEY_PREFIX::%")

Since I am explicitly saying which index to use, shouldn’t the meta().id validation be implicit and not required?

EDIT: Looks like USE INDEX is just a hint. Would love it if it meant more.

2

USE Index is hint. If the hint causes the wrong results (due to index not able to answer the query) it will ignore.

Index (simple_idx) has only items document keys starts with “KEY_PREFIX::” but your first query you are asking member = “TheOneIWant”. If there is document key that match predicate and starts with “ABC::” will not have in the simple_idx, this result in wrong results. So it will not use it. But second query is fine.

3

Yeah, mentioned that in my edit. We would definitely desire an option where USE INDEX fails if it doesn’t match, as we want to enforce our queries to be optimized with an index. We do not allow a primary index for exactly that reason.

Thanks.

4

Partial Index only considered when Index WHERE condition is present in query WHERE condition.
Index WHERE condition can be superset. The following query can use ix1 because k0 >10 is superset of k0 > 20

Example: CREATE INDEX ix1 ON default(k1) WHERE k0 > 10;
SELECT META().id FROM default WHERE k0 > 20 AND k1 = 30;

1 Like
5

When you give one or more index in the USE INDEX clause, we’ll still evaluate the query predicates to see if the index is qualified to evaluate the query.

Remember, the query result should be SAME with or without USE INDEX.
We don’t have any implied predicates.

See detailed articles at: https://blog.couchbase.com/n1ql-practical-guide/

1 Like