Couchbase N1QL queries don’t just need indexes; they are indexes, in a way, and without them, you’re just doing a full table scan, which is like asking a librarian to find a specific book by reading every single title on every single shelf.
Let’s see this in action. Imagine we have a bucket called travel-sample with documents like this:
{
"type": "hotel",
"name": "Grand Hyatt San Francisco",
"city": "San Francisco",
"country": "USA",
"iata": "SFO",
"geo": {
"lat": 37.79043,
"lon": -122.39976
},
"rating": 4,
"public_transport": ["BART", "Muni bus", "Streetcar"],
"amenities": ["wifi", "restaurant", "bar", "pool"]
}
Now, a common query might be to find all hotels in San Francisco:
SELECT name, rating FROM `travel-sample` WHERE city = "San Francisco";
Without an index, Couchbase has to read every single document in the travel-sample bucket, check if it has a city field, and if that field’s value is "San Francisco". For a small dataset, this is fine. For millions of documents, it’s a performance killer.
The solution is to create an index. An index is essentially a separate data structure that Couchbase maintains, mapping the values of indexed fields to the documents that contain them. When you query, Couchbase can use the index to quickly locate the relevant documents without scanning the entire bucket.
To create an index for our city field, we’d run this N1QL command:
CREATE INDEX idx_city ON `travel-sample`(city);
Now, when we run the query SELECT name, rating FROM travel-sample WHERE city = "San Francisco";, Couchbase will consult idx_city. It’ll find all document IDs associated with "San Francisco" in the index and then fetch only those documents. This is orders of magnitude faster.
But what about more complex queries? Consider this:
SELECT name, rating, amenities FROM `travel-sample` WHERE city = "San Francisco" AND rating = 5;
If we only have idx_city, Couchbase will use the index to find all hotels in "San Francisco," and then it will filter that result set for hotels with rating = 5. This is better than a full scan, but we can do even better.
We can create a composite index that covers both fields:
CREATE INDEX idx_city_rating ON `travel-sample`(city, rating);
With idx_city_rating, Couchbase can directly look up documents that match both "San Francisco" and 5 in a single index lookup. The order of fields in a composite index matters. (city, rating) is great for queries filtering on city or city and rating. It’s less effective for queries filtering only on rating.
What if we want to search for hotels that have a specific amenity, like "pool"?
SELECT name FROM `travel-sample` WHERE ANY amenity IN amenities SATISFIES amenity = "pool" END;
For queries involving array fields like amenities, we need to create an array index. This tells Couchbase to index each element within the array.
CREATE INDEX idx_amenities ON `travel-sample`(DISTINCT ARRAY amenity FOR amenity IN amenities END);
This allows Couchbase to efficiently find documents where "pool" exists within the amenities array.
The most important lever you control is how you define your indexes. Couchbase’s query optimizer will try its best to pick the most suitable index, but it can only work with what you provide. You can inspect the query plan using EXPLAIN to see which index is being used (or if none is being used, indicated by a ALL scan):
EXPLAIN SELECT name, rating FROM `travel-sample` WHERE city = "San Francisco";
The output might show something like idx_city being used. If it shows ALL, it means no suitable index was found, and Couchbase is scanning every document.
The one thing that trips many people up is understanding how COVER queries work. When you create an index, you can tell Couchbase to "cover" the query. This means all the data needed to satisfy the query (both the WHERE clause predicates and the SELECT list fields) is available directly within the index itself.
For example, if we have CREATE INDEX idx_city_name_rating ON travel-sample(city, name, rating); and we run SELECT name, rating FROM travel-sample WHERE city = "San Francisco";, Couchbase can satisfy this entire query by just reading idx_city_name_rating. It doesn’t even need to fetch the actual documents from the main data store. This is called a "covering index" or "index-only scan" and is the fastest possible query execution.
Beyond basic field indexes, Couchbase also supports geospatial indexes for location-based queries and full-text search indexes for more complex text analysis. Properly leveraging these different index types is key to unlocking N1QL’s performance potential.
The next thing you’ll likely encounter is optimizing for queries that involve OR conditions or complex LIKE patterns, which often require careful index design or alternative approaches.