Introduction to covering indexes

What are covering indexes?

In the relational/SQL world you can save query time by the judicious use of indexes: if you have an index on each column that your query touches then your DBMS can return the results without diving into the tables themselves.

These are covering indexes.

What are covering indexes in Couchbase?

Couchbase Server 4.1 brings covering indexes to N1QL. If you have an index that covers all of the data required by your N1QL query, Couchbase Server can return the data straight from that index. This cuts at least one, and possibly several, document reads from the query process.

How does it work?

When you submit a N1QL query to Couchbase, it goes through the following steps:

1. Parses your query, analyses what it does and then creates a query plan.
2. Checks the relevant indexes.
3. Gets the keys for the documents that contain the results and then fetches the documents themselves.
4. Pulls the relevant data out of those documents to create the result.
5. Returns the result to your application.

With a covering index, all the data for the result is in the index itself so you cut out steps 3 and 4.

How do I use covering indexes?

Nothing changes about how you write your queries. Instead, this is all about how you set-up the indexes. A single index — GSI or view — must cover every JSON key that your query touches.

Got an example?

Let's say we're storing customer profiles. They might look like this:

{

“name”: “Sherlock Holmes”,

“address”: “221b Baker Street”,

“city”: “London”,

“country”: “UK”,

“newsletter”: true,

“joinedDate”: 1168041600

}

If we wanted to find the name and city of everyone who is subscribed to our newsletter, we could write a N1QL query like this:

Without a covering index, this query would pull name and city from our customer records by doing a look-up of those documents.

We can create a covering index for this query like this:

Now the query can be satisifed entirely from the index.

Where can I read more?

There's a section on covering indexes in the Couchbase Server documentation.