Indexing your Data
Introduction
Querying documents using a pre-existing database index is much faster because an index narrows down the set of documents to examine — see: the Query Troubleshooting topic.
When planning the indexes you need for your database, remember that while indexes make queries faster, they may also:
-
Make writes slightly slower, because each index must be updated whenever a document is updated
-
Make your Couchbase Lite database slightly larger.
Too many indexes may hurt performance. Optimal performance depends on designing and creating the right indexes to go along with your queries.
|
Constraints
Couchbase Lite for c does not currently support partial value indexes; indexes with non-property expressions.
You should only index with properties that you plan to use in the query.
|
Creating a new index
You can use SQL++ or QueryBuilder syntaxes to create an index
Example 2 creates a new index for the type and name properties, shown in this data model:
{
"_id": "hotel123",
"type": "hotel",
"name": "The Michigander",
"overview": "Ideally situated for exploration of the Motor City and the wider state of Michigan. Tripadvisor rated the hotel ...",
"state": "Michigan"
}SQL++
The code to create the index will look something like this:
// For value types, this is optional but provides performance enhancements
// NOTE: No error handling, for brevity (see getting started)
// Syntax for second argument is the same as taking from a N1QL SELECT
// i.e. SELECT (type, name) FROM _;
CBLValueIndexConfiguration config = {
kCBLN1QLLanguage,
FLSTR("type, name")
};
CBLError err{};
CBLCollection_CreateValueIndex(collection, FLSTR("TypeNameIndex"), config, &err);Array Indexing
Couchbase Lite 3.2.1 introduces functionality to optimize querying arrays.
Array UNNEST to unpack arrays within a document to allow joins with the parent object, and array indexes for indexing unnested array’s values to allow more efficient queries with UNNEST.
The Array Index
An array index is a new type of the index for indexing nested array’s properties to allow querying with the UNNEST more efficiently.
Below is an example array index configuration:
CBLArrayIndexConfiguration config = {
kCBLN1QLLanguage,
FLSTR("contacts")
};Array Index Syntax
The syntax for array index configuration is shown below:
| Name | Is Optional? | Description |
|---|---|---|
|
|
Path to the array to be indexed.
Use |
|
|
An optional array of strings where each string represents values within the array to be indexed. In N1QL/SQL++ syntax, these expressions are separated by commas. In JSON syntax, as supported by Couchbase Lite for C, the expressions are represented as a JSON array. If the array specified by the path contains scalar values, the expressions should be left unset or set to null. |
Using Array Indexes with UNNEST
For the following examples, you can assume we are querying results from the following document, shown below:
{
"Name":"Sam",
"contacts":[
{
"type":"primary",
"address":{"street":"1 St","city":"San Pedro","state":"CA"},
"phones":[
{"type":"home","number":"310-123-4567"},
{"type":"mobile","number":"310-123-6789"}
]
},
{
"type":"secondary",
"address":{"street":"5 St","city":"Seattle","state":"WA"},
"phones":[
{"type":"home","number":"206-123-4567"},
{"type":"mobile","number":"206-123-6789"}
]
}
],
"likes":["soccer","travel"]
}Using the document above you can perform queries on a single nested array like so:
SELECT name, interest FROM _ UNNEST likes as interest WHERE interest = "travel"The query above produces the following output from the document:
{"name": "Sam", "like": "travel"}You can also perform the same operation using array indexes like so:
CBLArrayIndexConfiguration config = {
kCBLN1QLLanguage,
FLSTR("likes")
};
CBLError err{};
CBLCollection_CreateArrayIndex(collection, FLSTR("myindex"), config, &err);You can perform similar operations on nested arrays:
SELECT name, contact.type, phone.number
FROM profiles
UNNEST contacts as contact
UNNEST contact.phones as phone
WHERE phone.type = "mobile"The query produces the following output:
{"name": "Sam", "type": "primary", "number": "310-123-6789"}
{"name": "Sam", "type": "secondary", "number": "206-123-6789"}The output demonstrates retrieval of both primary and secondary contact numbers listed as type "mobile".
Here’s an example of creating an array index on a nested array containing dictionary values:
CBLArrayIndexConfiguration config = {
kCBLN1QLLanguage,
FLSTR("contacts[].phones"),
FLSTR("type")
};
CBLError err{};
CBLCollection_CreateArrayIndex(collection, FLSTR("myindex"), config, &err);The above snippet creates an array index to allow you to iterate through contacts[].phones[].type in the document, namely "home" and "mobile".
| Array literals are not supported in CBL 3.2.1. Attempting to create a query with array literals will return an error. |