"error":"no_usable_index", "reason":"No index exists for this sort, try indexing by the sort fields."

[hamiltonsalazar]

Description

I followed the instructions in the documentation for POST /{db}/_find, but that doesn't work for me

Current Behaviour

I have a database "views" with different documents. I want to find documents using a declarative JSON querying syntax, i try with the example query in the documentation:

Accept: application/json
Content-Type: application/json
Content-Length: 168
Host: localhost:5984

{
    "selector": {
        "year": {"$gt": 2010}
    },
    "fields": ["_id", "_rev", "year", "title"],
    "sort": [{"year": "asc"}],
    "limit": 2,
    "skip": 0,
    "execution_stats": true
}

For me i use the below query:

curl -X POST http://anna:secret@127.0.0.1:5984/views/_find -H "Accept: application/json" -H "Content-Type: application/json" -d "{ \"selector\": { \"year\": {\"$gt\": 2010} }, \"fields\": [\"_id\", \"_rev\", \"year\", \"title\"], \"sort\": [{\"year\": \"asc\"}], \"limit\": 2, \"skip\": 0, \"execution_stats\": true}"

But CouchDB responded with:
{"error":"no_usable_index","reason":"No index exists for this sort, try indexing by the sort fields."}

Expected Behaviour

When i query CouchDB would response with:

{"_id":"4d09da4324f9677d997396179c00b05f","_rev":"9-4ca2559289e18e87aadd6d024f8d6d83","year":2011,"title":"FishStew"}, {"_id":"4d09da4324f9677d997396179c021aae","_rev":"3-6791270dc777bf9f260961858e7b569e","year":2019,"title":"BeefStew"} ], "bookmark": "g1AAAABweJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYormKQYWKYkmhgbmaRZmpmbp1hamhtbmhmaWyYbGBkmJqaC9HHA9BGlIwsAd-sdew", "execution_stats": {"total_keys_examined":0,"total_docs_examined":3,"total_quorum_docs_examined":0,"results_returned":2,"execution_time_ms":1.0},

Your Environment

• CouchDB version used: 3.1.0
• Operating system and version: Windows 10 Pro 1909

[wohali]

Did you try creating an index on the sort fields as the output suggested?

That would be using this endpoint.

[garrensmith]

@wohali is correct. You need an index on year before you can sort by year. If you remove the above sort field it will work without the index.

[lexicouch]

@garrensmith : I have created index, but still I am getting same error.

[hamiltonsalazar]

Thank you! This is the answer.

[lexicouch]

Hi, can you share your couch db query. I m getting the same error. I have created index also.

[vmatyus]

I've experienced the same problem, but only with text typed indexes like:

curl -X POST "$SERVICE_URL/$DB/_index" --data '{
    "ddoc": "text-index",
    "index": {
      "fields": [{
        "name": "name",
        "type": "string"
      }]
    },
    "name": "getUserByName",
    "type": "text"
}'

I wanted to sort based on name field with:

curl -X POST "$SERVICE_URL/$DB/_find" --data '{
  "fields": ["_id", "type", "name", "email"],
  "limit": 3,
  "selector": {
    "email_verified": {
      "$eq": true
    }
  },
  "sort": [{
    "name": "desc"
  }]
}'

A document that this query shall return:

{
  "_id": "abc123",
  "type": "user",
  "name": "Bob Smith",
  "email": "bob.smith@aol.com",
  "email_verified": true,
}

Current behaviour

_find returns:

statusText: 'Bad Request',
  status: 400,
  code: 400,
  body: '{"error":"no_usable_index","reason":"No index exists for this sort, try indexing by the sort fields."}',

Expected behaviour

_find should return:

{
  docs: [
    {
      _id: 'abc123',
      type: 'user',
      name: 'Bob Smith',
      email: 'bob.smith@aol.com'
    }
  ],
  bookmark: 'abcdefg'
}

Environment

I've used CouchDB "version":"3.2.0","git_sha":"efb409bba" (apache/couchdb docker image)
During manual testing the execution stoped many times unexpectedly, so the index handling itself is quite fragile in this version.
Did I use the latest version?

Additional context

If I add email_verified as an index, then the above described query works.

curl -X POST "$SERVICE_URL/$DB/_index" --data '{
    "ddoc": "text-index",
    "index": {
      "fields": [{
        "name": "name",
        "type": "string"
      },
      {
        "name": "email_verified",
        "type": "boolean"
      }]
    },
    "name": "getUserByName",
    "type": "text"
}'

Why I have to define email_verified also as a text index (I use it in selector, not in the sort)?