Why does a Couchbase SQL++ query fail with "No index available" when the documents exist?
answer
- The query engine cannot scan on its own
- It needs an access path from another service
- One index type covers every document key
- Naming keys directly skips the whole problem
- There is a statement that recommends indexes
basics
~20 sCouchbase's Query service reaches documents only through an index. With no primary index and no secondary GSI whose leading key matches a predicate, there is no access path, so the statement is rejected rather than scanning the whole collection.
solid answer
~50 sUnlike engines that fall back to a table scan, the Couchbase Query service will not sweep a collection on its own. It plans a statement by looking for an access path in the Index service: either a **global secondary index (GSI)** whose leading key is constrained by the query, or a **primary index**, which indexes every document key in the collection. If neither exists the query is refused with a "no index available" error. The three ways out are: create a targeted secondary index on the predicate fields, add `USE KEYS` when you already know the document keys (that goes straight to the Data service and needs no index), or create a primary index. A primary index is fine while developing but is a production liability — every query that falls back to it scans all keys and then fetches the documents. Run `ADVISE` on the statement to get an index recommendation.
code
sql · 6 lines-- development shortcut: every query runs, every query scans
CREATE PRIMARY INDEX ON `app`.sales.orders;
-- production: index the predicate instead
CREATE INDEX idx_orders_status_created
ON `app`.sales.orders(status, created_at);go deeper
Know that Couchbase requires an index before a SQL++ query can find documents, and that creating a primary index makes queries run while you are developing.
Explain the access-path rule: a secondary index whose leading key the query constrains, a primary index, or USE KEYS. Be ready to say why the primary index is a development convenience rather than a production answer.
Diagnose the case where an index exists but is not eligible, use ADVISE deliberately, and consolidate recommendations into fewer composite indexes rather than accumulating overlapping ones.
Own the policy: no primary indexes in production so missing access paths fail loudly, a review path for new indexes given their write-amplification and index-service memory cost, and an index inventory that is maintained rather than grown.
## The design decision behind the error Most SQL engines answer an unindexed predicate with a full table scan: slow, but it returns rows. Couchbase deliberately does not. The Query service is a separate service from the Data service, and the only way it can enumerate documents is through the Index service. If planning finds no usable access path, the statement fails immediately with an error saying no index is available for the keyspace. The point is to make an unbounded, cluster-wide scan an explicit choice rather than an accident. ## What counts as an access path Three things can give the planner a path: 1. **A secondary GSI whose leading index key is constrained by the query.** `CREATE INDEX ix ON app.sales.orders(status, created_at)` serves `WHERE status = "OPEN"` and `WHERE status = "OPEN" AND created_at > ...`, but is not eligible for a query that only filters on `created_at`, because the leading key is unconstrained. The index also contains only documents where the leading key is present — documents missing `status` are not in it at all. 2. **A primary index.** `CREATE PRIMARY INDEX ON app.sales.orders` indexes every document key in the collection, so any predicate becomes answerable: scan all keys, fetch all documents, filter in the Query service. 3. **`USE KEYS`.** `SELECT * FROM app.sales.orders USE KEYS ["order::1","order::2"]` names the documents directly. The Query service asks the Data service for those keys and never touches the Index service, so this works on a collection with no indexes at all. ## Why a primary index is a development tool A primary index makes every query "work", which is exactly why it is dangerous: it hides missing indexes. Any statement that falls back to it does a full keyspace scan followed by a document fetch for each surviving key, which grows linearly with the collection and burns index, query and data resources at once. It also has to be maintained on every mutation. The common production convention is to create primary indexes freely in development, then drop them before go-live so that a missing index shows up as a loud error rather than as a slow query — the error is more useful than the timeout. ## Getting the right index instead Couchbase ships an index advisor. Prefixing a statement with `ADVISE` returns recommended indexes for that statement without running it: ```sql ADVISE SELECT o.id, o.total FROM `app`.sales.orders WHERE o.status = "OPEN" AND o.total > 100; ``` The output distinguishes indexes that would be newly needed from existing indexes that already cover part of the predicate. The Query Workbench exposes the same thing as an Index Advisor panel. Treat its output as a starting point, not a verdict: it advises per statement, so blindly applying every recommendation across a workload produces overlapping indexes that all cost write amplification. Consolidate — one composite index with the right leading key often serves several statements. ## Collection-scoped indexes In Couchbase 7.x indexes are created on a collection, using the full keyspace path. That means a query confined to one collection no longer needs the old `WHERE type = "order"` discriminator, and the index no longer needs `type` as its leading key. Teams migrating from a pre-7.0 single-bucket layout usually get a free win here: dropping the type predicate shortens every index and improves its selectivity. ## Diagnosing in practice When the error appears, the checklist is: does the keyspace path point at the collection you think (a typo silently names a keyspace with no indexes); is there an index whose *leading* key your `WHERE` clause constrains; is the query filtering only on a field that appears second or later in a composite index; and could the statement be rewritten with `USE KEYS` because the caller actually knows the key. A very large fraction of real Couchbase performance work is the second case: an index exists, but not one the planner can use, so either the query is rewritten or an index with the right leading key is added. ## What weak answers sound like Candidates who say Couchbase "just scans the bucket if there is no index" have not run the system. Candidates who fix every such error with `CREATE PRIMARY INDEX` have run it only in development. The strong answer names the access-path requirement, reaches for a targeted secondary index, and mentions `USE KEYS` as the zero-index path for known keys.
- Why is leaving a primary index in place in a Couchbase production cluster discouraged?Because it silently rescues every unindexed query. A statement that falls back to it scans all document keys in the collection and then fetches each surviving document, so cost grows with collection size and the missing index never surfaces. It also has to be maintained on every mutation. Dropping it converts a slow query into an explicit error.
- An index exists on a Couchbase collection but the query still reports no index available. What is the usual cause?The query does not constrain the index's leading key. A composite GSI on `(status, created_at)` is not eligible for a predicate on `created_at` alone. Either add an index whose leading key matches the predicate, or restructure the query. Documents missing the leading key are also absent from the index entirely.
- How can a Couchbase SQL++ statement return documents from a collection that has no indexes at all?By naming the documents with `USE KEYS`, for example `SELECT * FROM app.sales.orders USE KEYS ["order::1"]`. The Query service resolves those keys against the Data service directly, bypassing the Index service, so no primary or secondary index is required.
saying these in an interview costs you the question
- Says Couchbase falls back to a full collection scan
- Fixes every such error with CREATE PRIMARY INDEX
- Thinks any index on the collection makes queries work
- Ignores that the leading index key must be constrained
- Applies every advisor recommendation without consolidating