Couchbase没有可用的索引 [英] Couchbase No Index Available

问题描述

我们在使用Couchbase N1QL查询时遇到问题.

We are having problems with a couchbase N1QL Query.

我们的索引定义如下:

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')

并出现以下错误:

键空间门户上没有与您的查询匹配的索引.
使用CREATE INDEX或CREATE PRIMARY INDEX创建索引，
或检查您的预期索引是否在线.

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.

有什么想法吗?

我创建了另一个没有WHERE子句的索引，它不再返回错误.

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`)

唯一的问题是需要WHERE子句！

The only problem is that the WHERE clause is required!

推荐答案

您创建的索引是部分索引(即，索引具有WHERE子句，仅具有满足条件的条目).为了使查询使用该索引，必须符合条件(即查询where子句必须是索引的子集，where子句+查询谓词必须具有前导索引键)，否则选择该索引可能会导致错误的结果，并且查询优化器不会选择该索引

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.

与 AppUser％一样，它也不正确，它必须是单引号或双引号，而不是反引号.

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%";

在Couchbase N1QL中设计查询查询索引 https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf

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

这篇关于Couchbase没有可用的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！