Issues with secondary indexes

[kishmiryan-karlen]

Hello, I'm using Ottoman.js v2.2.0. I have a few models with different types of indexes. When I start the application (which calls await ottoman.start() I see the following issues:

1. I have the following index on a few models, however the index is created only for the first one:

mySchema.index.findById = {
  by: '_id',
  type: 'n1ql'
};

2. On the CB Admin UI I don't see refdoc indexes, defined like this:

mySchema.index.findByTest = {
  by: 'test',
  type: 'refdoc'
};

3. All index names are prefixed by __default__default. I guess that's the collection name + scope name, but is there a way to specify a custom name for an index? (I couldn't find any prop with TS autocomplete)

4. There's a default index being created with some strange name. Why is it created and why does it have such a name?

5. I see the following output when debug mode is enabled. Seems it tries to create the default index for each of the models (I have 7 models, so the first one is succeeded and 6 are failed). Also instead of seeing the other index names, it outputs just undefineds.

6. I created two indexes: one based on field A and field B and another one based on field A only, like the following:

/* first index */
mySchema.index.findByAandB = {
  by: ['A', 'B'],
  type: 'n1ql'
};

/* second index */
mySchema.index.findByA = {
  by: 'A',
  type: 'n1ql'
};

When I run a query like this: select * from test_bucket where A="test", the Advice tab shows that the second index has been used (which is correct).

When I run the following query: select * from test_bucket where A="test" and B="test", the Advice tab shows that the first index has been used (also correct).

However, when I run the first query again after the second one, it always shows that the first index is being used although there's no condition on field B in the query.

Thanks

[xavierchow]

However, when I run the first query again after the second one, it always shows that the first index is being used although there's no condition on field B in the query.

This is possible, actually you don't need the index findByA at all as it's already covered by findByAandB.

Under the hood, the couchbase just concatenates the fields you specified for the index search. So suppose we have those documents

{ A: 'foo', B: 'bar' }  // doc-id: 001
{ A: 'foo', B: 'bar1' }  // doc-id: 002
{ A: 'foo1', B: 'bar2' } // doc-id: 003

with the findByAandB index, the look-up map is conceptually similar to the following key-values,

foo_bar => 001
foo_bar1 => 002
foo1_bar1 => 003

When you query with where A="foo", the search engine just looks for docs that matches the pattern below,

foo_*

(it's filtered in alphabetical order if I remember correctly.)

@kishmiryan-karlen

[kishmiryan-karlen]

@xavierchow thanks for your answer. Is it true also if I had findByB? I mean, is there also a lookup like this *_bar?

Also, one more important thing to note is that, although the first query ends up using the findByAandB index, the Advice tab still suggests to create findByA index as well, when it's not there. How would you explain it?

[xavierchow]

I mean, is there also a lookup like this *_bar?

No, the lookup just matches from the beginning, that's why the orders of fields matter. i.e. if you want to query B field only, you probably have to define another index that starts from B.

the Advice tab still suggests to create findByA index as well

Not quite sure which advice tab you mean, I'm using Community Edition 7.0.2 , don't find an advice tab from the Query UI.

[kishmiryan-karlen]

@xavierchow thanks for your answer.

No, the lookup just matches from the beginning, that's why the orders of fields matter. i.e. if you want to query B field only, you probably have to define another index that starts from B.

Got it!

As for the second part, you can see the tab I'm talking about in the image below:

When you run a query it shows which indexes have been used for it and if there are better options it recommends new indexes to be created.

Also, I figured out the answer to my question (why it still suggest to create a findByA) and the reason is that there are extra fields in the query, which are not part of an index, like field "C" in the query above. If I remove it there's no more recommendations on better indexes.

And do you happen to know the answers to my other 5 questions? :)

[xavierchow]

@kishmiryan-karlen for the 2nd question, the refDoc index is not a couchbase concept but an implementation by ottoman, so you can't find anything about it from the couchbase admin UI. https://ottomanjs.com/guides/schema.html#refdoc

for the other questions, sorry I can't give the answers unless... there is a code sample that re-produces the problem.

[kishmiryan-karlen]

@xavierchow thanks for the refDoc reference! Unfortunately I can't put some code together for those cases right now. Anyway, appreciate your help!

@gsi-alejandro would you be able to address some of my questions?

[gsi-alejandro]

hi @kishmiryan-karlen

Sorry the delay

1. I'm checking it, probably I will add modelName into the index name in order to fix the issue and create 1 index per Model, the next release will have this fix.

Recommendation: I think a better way and more efficient will be to use idKey: "_id" while creating the model to use the Couchbase's key/value operations that are faster and don't will need an index and the extra store for it. (Of course, we will fix the issue above, share this alternative because I think is a lot better)

2. Refdoc is implemented and maintained in Ottoman, no index is created on Couchbase DB Server.

3. Now there's no way the user can provide an index name, but we'll discuss it and probably provide a way to allow users to name the index.

4. It's a bug, that will be fixed in the next release. (I forgot to scape a dot in a regular expression)

5. It's a bug, that will be fixed in the next release. (Should only print if the value is defined)

6. It's related to Couchbase Server behavior, I will check about it, need to research, and maybe post an issue on a Couchbase Forum. I will let you know as soon I have a reply about it.

[kishmiryan-karlen]

@gsi-alejandro thanks a lot for all your answers! I'm going to use the idKey recommendation for sure and will be waiting for the next releases with fixes. Do you know roughly what's the ETA of the next release?

[gsi-alejandro]

@kishmiryan-karlen the next release will be this week or the next one.

By the way, I already fixed these issues.

[kishmiryan-karlen]

@gsi-alejandro thanks a lot!