Indexing
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 ios 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:
let config = ValueIndexConfiguration(["type", "name"])
try collection.createIndex(withName: "TypeNameIndex", config: config)QueryBuilder
| See the QueryBuilder topic to learn more about QueryBuilder. |
The code to create the index will look something like this:
let index = IndexBuilder.valueIndex(items: ValueIndexItem.expression(Expression.property("type")),
ValueIndexItem.expression(Expression.property("name")))
try collection.createIndex(index, name: "TypeNameIndex")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:
let config = ArrayIndexConfiguration(path: "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:
try collection.createIndex(withName: "singleIndex", config: ArrayIndexConfiguration(path: "likes", expressions: ["type"]))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:
try collection.createIndex(withName: "nestedArray", config: ArrayIndexConfiguration(path: "contacts[].phones", expressions: ["type"]))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. |