Can't find matching index with CouchDB and sort

Question

I have the following query:

{
   "selector":{
      "lastname":{
         "$regex":"(?i)[cç][oòóôõöø]"
      },
      "firstname":{
         "$gt":null
      },
      "type":"person",
      "owner":{
         "$in":["admin"]
      }
   },"sort":["lastname","firstname"]
}

And tried many indexes:

{
 "type": "json",
 "def": {
  "fields": [
   {
    "lastname": "asc"
   }
  ]
 }
}

{
 "type": "json",
 "def": {
  "fields": [
   {
    "lastname": "asc"
   },
   {
    "firstname": "asc"
   }
  ]
 }
}

{
 "type": "json",
 "def": {
  "fields": [
   {
    "lastname": "asc"
   },
   {
    "firstname": "asc"
   },
   {
    "type": "asc"
   },
   {
    "owner": "asc"
   }
  ]
 }
}

But none worked. FYI I'm using CouchDB 2.1.0.

I also tried to add "sort":["lastname","firstname","type","owner"] to the query. Still getting the warning: no matching index found, create an index to optimize query time

What am I doing wrong?

Edit: I'm using PouchDB directly to my CouchDB server (no sync), if that can help...

My guess is, the first element in the sort must be in the selector and use an operator that use index( regex doesn't) — Alexis Côté, Nov 01 '17 at 23:06
@AlexisCôté I tried to put type in first position in the selector but still getting the warning. I also added type in the sort, didn't help either... — Nate, Nov 02 '17 at 23:19

score 0 · Answer 1 · answered Nov 28 '17 at 11:41

0

Have you created the index before?

this._DB.createIndex({
   index: { fields: ['lastname', 'firstname'] },
   ddoc: "my-index-design-doc"
})

And use it in your .find than with use_index: 'my-index-design-doc' ?

answered Nov 28 '17 at 11:41

bastifix

443
1
6
18

score 0 · Answer 2 · answered Apr 24 '20 at 04:44

As a side note, bear in mind the consequences in performance that you query will have will be drastic in large databases. Operators $in and $regex will process the whole database in memory, since it does not uses any index —nor can, at least at the latest CouchDB version: 3.0.2.

On the other hand, $eq, $gt — $gte, $lt — $lte will indexes without processing all data. This is where it gets clever.

Can't find matching index with CouchDB and sort

2 Answers2