CouchDB query no index exists for this sort error

Question

I have seen already this question a lot of times, yet here I am asking same question.

Why do I get this error:

Error running query. Reason: (no_usable_index) No index exists for this sort try indexing by the sort fields.

I am running a blockchain service with hyperledger fabric and I am using CouchDB for the World State. In these data I am having a field for date and I want to sort data by date. This is my Mango query:

{
   "selector": {
      "date": {
         "$gte": null
      },
      "_id": {
         "$gte": null
      }
   },
   "fields": [
      "_id",
      "date"
   ],
   "sort": [
      {
         "date": "asc"
      }
   ]
}

To be mentioned, date is a string. I don't have any idea what is going on here since I tried everything. I tried sorting with any field of the table but the only one to get sorted and working is with the field : _id

I even tried with the _rev field which is again under the _id and still error.

{
   "selector": {
      "_rev": {
         "$gte": null
      },
      "_id": {
         "$gte": null
      }
   },
   "fields": [
      "_id",
      "_rev"
   ],
   "sort": [
      {
         "_id": "asc"
      },
      {
         "_rev": "asc"
      }
   ]
}

This is an example of my data:

{
 "id": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
 "key": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
 "value": {
  "rev": "1-4f2cc5b932d393a88b3497a3942fff33"
 },
 "doc": {
  "_id": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
  "_rev": "1-4f2cc5b932d393a88b3497a3942fff33",
  "company": "greenTea",
  "date": "2022-02-04 15:40:23.337 +0000 UTC",
  "fromacc": "Gary",
  "operation": "",
  "txid": "438c5ad0868db83201bdc36edb7705e3081c73821b20d14d4c4251af3e49c04e",
  "type": "transaction",
  "~version": "CgMBBwA="
 }
}

I have tried anything I have found online but I cannot get it to work. Any help or suggestions or even a good documentation would be appreciated.

Thank you

I am curious, why sort on `_rev`? Removing that from the sort will produce results. — RamblinRose, Feb 11 '22 at 16:34
Just to test if it works with a field that lives near _id. If you check the first sort that I posted is exactly what you say. — Rafail K., Feb 11 '22 at 16:34
Imagine what would happen if there where 10 billion documents to sort in memory. You must create an index on `date` if you want to sort `date`. — RamblinRose, Feb 11 '22 at 16:47

RamblinRose · Accepted Answer · 2022-02-11T17:03:27.357

Aside from the full table scan required to gather results, imagine what would happen if there where 10 billion documents to sort in memory.

It is required to create an index on date if you want to sort on the field.

Simply add an index like this.

{
   "index": {
      "fields": [
         "date"
      ]
   },
   "name": "date-index"
}

And you will find this query succeeds:

{
   "selector": {
      "date": {
         "$gte": null
      },
      "_id": {
         "$gte": null
      }
   },
   "fields": [
      "_id",
      "date"
   ]
}

There may be some efficiency with this query

{
   "selector": {
      "date": {
         "$gte": 9
      },
      "_id": {
         "$gte": 9
      }
   },
   "fields": [
      "_id",
      "date"
   ]
}

Since both fields are string types, see 3.2.2.5. Collation Specification

You are absolutely right!! Thank you very much! I red about index and I get what you mean. Thank you very much for you answer. Its clear now. — Rafail K., Feb 11 '22 at 17:27

CouchDB query no index exists for this sort error

1 Answers1