couchbasesql++

Couchbase No Index Available

We are having problems with a couchbase N1QL Query.

We have an index defined as follows:

CREATE INDEX `AppUser_SubjectId3` ON `Portal`(`SubjectId`) WHERE ((meta(self).`id`) like `AppUser%`)

We are then trying to run the following query:

SELECT RAW `Extent1` 
FROM `Portal` as `Extent1` 
USE INDEX (`AppUser_SubjectId3` USING GSI) 
WHERE (`Extent1`.`SubjectId` = 'c8ea08231c3a985a06342355b87d6e2d6290a985d5c3592e5b8e5e5f14608a08')

And get the following error:

No index available on keyspace Portal that matches your query.
Use CREATE INDEX or CREATE PRIMARY INDEX to create an index,
or check that your expected index is online.

We have confirmed the obvious that the index is online. The only item worth noting is that the Bucket does not actually contain any documents at the moment, but we would not expect this error in this instance, simply nothing to be returned.

Any ideas?

EDIT:

I have created another index without the WHERE clause and it does not return the error any longer.

CREATE INDEX `AppUser_SubjectId4` ON `Portal`(`SubjectId`)

The only problem is that the WHERE clause is required!

Solution

  • The Index You created is partial index (i.e Index has WHERE clause, only has entries that satisfies where condition). For query to use that index it must qualify (i.e query where clause must be subset of index where clause + query predicate must have leading index key) other wise by choosing that index it can result in wrong results and query optimizer will not choose that index.

    Also like AppUser% is incorrect it must be single or double quotes not back-ticks.

    CREATE INDEX `AppUser_SubjectId3` ON `test`(`SubjectId`) 
WHERE meta().`id` like "AppUser%";


SELECT RAW e 
FROM `Portal` AS e 
USE INDEX (`AppUser_SubjectId3` USING GSI) 
WHERE e.`SubjectId` = 'c8ea08231c3a985a06342355b87d6e2d6290a985d5c3592e5b8e5e5f14608a08'
      AND META(e).id LIKE "AppUser%";

    Designing Index For Query In Couchbase N1QL https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf