When does a CouchDB _find query fall back to a full database scan, and how do you detect it?

## What Mango is Mango is CouchDB's declarative query API: `POST /db/_find` with a JSON body containing a `selector`, plus optional `fields`, `sort`, `limit`, `skip`, `use_index` and `bookmark`. Selectors use operators like `$eq`, `$gt`, `$lt`, `$in`, `$and`, `$or`, `$exists`, `$type`, `$size`, `$regex`, `$elemMatch` and `$allMatch`. It exists because writing a design document for every access path is heavy, and it reads far more naturally than an `emit`-and-range-scan view. What it is *not* is a cost-based optimizer over arbitrary data. It picks among indexes you have explicitly declared, and when none fits it does the only other thing it can. ## The fallback Every database has one index it always has: `_all_docs`, ordered by document id. When a selector has no better candidate, Mango uses that — meaning it reads every document in the database and evaluates the selector in memory. Results are correct. Latency is proportional to database size, and it stays acceptable through development and the first weeks of production, then degrades steadily. CouchDB does tell you. The `_find` response carries a `warning` field stating that no matching index was found and that creating one would improve query time. Because it is a field in a successful `200` response rather than an error, client libraries and application code routinely discard it. Logging or asserting on that field in a test environment is the cheapest possible guard against shipping full-scan queries. ## Declaring an index ```json POST /db/_index {"index": {"fields": ["type", "createdAt"]}, "name": "type-created", "type": "json"} ``` A `json` index is ordered on the listed fields, and — as with any ordered composite index — usefulness follows the leading fields: an index on `["type", "createdAt"]` serves a selector on `type` alone, or on `type` plus a range on `createdAt`, but not a selector on `createdAt` alone. Under the hood these indexes are ordinary map/reduce views stored in design documents, which has a practical consequence: a newly created index has to be built, and the first query that needs it may block while that happens. `GET /db/_index` lists what exists; `DELETE /db/_index/{ddoc}/json/{name}` removes one. An index may also carry a `partial_filter_selector`, which restricts which documents are indexed at all — the right tool when you query only active records out of a database dominated by archived ones, because it keeps the index small. ## Diagnosing with _explain `POST /db/_explain` takes exactly the body you would send to `_find` and returns the plan: which index was chosen, the start and end keys of the range it will scan, and the residual selector applied to the rows that come back. Two patterns to recognize immediately: - the chosen index is `_all_docs` — you are scanning the database; - an index was chosen but the scan range spans the whole index — you are scanning that index end to end, which is better than scanning documents but is still linear. If CouchDB picks a worse index than you expect, `use_index` in the `_find` body pins the choice. ## Sorting `sort` cannot be satisfied by re-ordering results in memory. The requested order must be produced by an index whose fields provide it, and the selector must constrain the fields being sorted on; otherwise the query fails with an error saying no index exists for that sort. This surprises people coming from engines that will happily sort a small result set — in Mango, sorting is an index property, not a post-processing step. ## Operators that cannot narrow Not every operator becomes a range. `$regex`, `$ne`, `$exists: false` and similar negative or pattern predicates cannot restrict the index scan; CouchDB uses whatever other clauses can constrain the range, then applies these to each row it fetched. A selector consisting of nothing but a `$regex` is a full scan wearing a costume. Combine it with an equality on an indexed field so the scan has boundaries. ## Pagination `_find` paginates with a `bookmark` returned in each response and passed into the next request, not with growing `skip` values — `skip` still has to walk the rows it discards. Pass the bookmark through and page size stays cheap. ## Choosing Mango or a view Mango is right for ad-hoc and moderately varied predicates and for anything a human is composing. Reduced map/reduce views remain right for aggregates, for compound-key rollups such as `group_level` queries, and for the two or three highest-volume access paths where you want to control exactly what is stored in the index. A healthy CouchDB application typically uses both. ## Interview framing Say it plainly: Mango does not invent indexes. No matching declared index means a full scan through `_all_docs` plus a warning in an otherwise successful response, and `_explain` is how you find out before your users do.