This feature is available in SDK version 4.12.0 and later.
DQL indexing allows you to create simple indexes on the Small Peer to accelerate searches, support joins, and enhance the overall efficiency of data retrieval.

​
Index Operations Overview

DQL supports the following index operations:
OperationDescription
CREATE INDEXCreates a simple on a specified field
DROP INDEXRemoves an existing index
USE INDEXForce a query to use a specific index
USE DIRECTIVESAdvanced control over index usage and query optimization
SELECT from system:indexesLists all indexes in the database
Index operations can only be performed through the execute SDK operation. Using any of these commands through registerObserver or registerSubscription will fail with an “invalid query not supported” response.

​
Creating Indexes

​
Simple Index

Create an index on a single field to improve query performance for that field:
DQL
-- Standard syntax
CREATE INDEX index_name ON collection_name (field1)

-- Alternative syntax
CREATE INDEX collection_name.index_name (field1)

-- With IF NOT EXISTS clause (prevents errors on duplicate index names)
CREATE INDEX IF NOT EXISTS index_name ON collection_name (field1)
For example, create an index on the color field in the cars collection:
DQL
CREATE INDEX color_idx ON cars (color)
-- or
CREATE INDEX cars.color_idx (color)
-- or with IF NOT EXISTS to allow repeated execution
CREATE INDEX IF NOT EXISTS color_idx ON cars (color)
The IF NOT EXISTS clause allows the statement to execute successfully even if an index with the same name already exists on the collection. This is useful for initialization scripts that may run multiple times.
The IF NOT EXISTS clause only checks if an index with the same name exists - it does not verify the field definition. If an index with the same name already exists (even on a different field), the CREATE INDEX IF NOT EXISTS will succeed without creating a new index or modifying the existing one. To change an index definition, you must explicitly DROP the existing index first before creating the new one.

​
Index Creation Examples

The CREATE INDEX operation returns an empty result set (no items) upon successful completion. Successful execution without errors indicates that the index was created successfully - if there are any issues (such as duplicate index names or invalid syntax), an error will be thrown. To verify that your index was created successfully, you can query the system:indexes collection.
// Create a simple index
let result = await ditto.store.execute(
  query: "CREATE INDEX color_idx ON cars (color)")
// result.items will be empty - this is expected

// Create with IF NOT EXISTS - safe for repeated execution
let result2 = await ditto.store.execute(
  query: "CREATE INDEX IF NOT EXISTS color_idx ON cars (color)")
// No error even if index already exists

// Verify the index was created
let indexes = await ditto.store.execute(
  query: "SELECT * FROM system:indexes WHERE _id = :indexName",
  arguments: ["indexName": "cars.color_idx"])
// indexes.items will contain the index details

​
Nested Fields

You can create indexes on nested fields by using dot notation. You can create indexes on registers nested within a map, as long as the value at the end of the path is a scalar.
DQL
CREATE INDEX nested_idx ON cars (properties.engine.type)

​
LIKE

Indexes can also accelerate LIKE queries when searching for prefix patterns:
DQL
-- Create an index on the name field
CREATE INDEX name_idx ON users (name)

-- This query can use the name_idx for efficient prefix matching
SELECT * FROM users WHERE name LIKE 'John%'
Indexes are particularly effective for LIKE patterns that start with a fixed prefix (e.g., ‘prefix%’). Patterns starting with wildcards (e.g., ‘%suffix’) cannot leverage indexes effectively.

​
Automatic Index Selection

By default, DQL automatically chooses the most appropriate index for your queries based on the WHERE clause conditions and ORDER BY expressions:
DQL
SELECT * FROM cars WHERE color = 'blue'
-- Automatically uses color_idx if it exists
When a query contains both WHERE and ORDER BY clauses, DQL considers both when selecting the optimal index. An index that satisfies both the filter condition and sort order will be preferred over one that only satisfies the filter.

​
Operating on Indexes

DQL supports additional options when creating indexes:

​
Viewing Indexes

You can discover all indexes in your database by querying the special system:indexes collection:
DQL
SELECT * FROM system:indexes
Each document in the system:indexes collection follows this schema: 
{
  "_id": "index_name",
  "collection": "collection_name", 
  "fields": ["field1", "field2"]
}
Example 
{
  "_id":"tasks.tasks_id",
  "collection":"tasks",
  "fields":["`_id`"]
}       

// List all indexes
let result = await ditto.store.execute(
  query: "SELECT * FROM system:indexes")

// List indexes for a specific collection
let result = await ditto.store.execute(
  query: "SELECT * FROM system:indexes WHERE collection = :collectionName",
  arguments: ["collectionName": "cars"])

​
Dropping Indexes

Remove an index that is no longer needed:
DQL
-- Standard syntax
DROP INDEX index_name ON collection_name

-- Alternative syntax
DROP INDEX collection_name.index_name

-- With IF EXISTS clause (prevents errors if index doesn't exist)
DROP INDEX IF EXISTS index_name ON collection_name
For example, to drop the color_idx index from the cars collection:
DQL
DROP INDEX color_idx ON cars
-- or
DROP INDEX cars.color_idx
-- or with IF EXISTS to allow repeated execution
DROP INDEX IF EXISTS color_idx ON cars
The IF EXISTS clause allows the statement to execute successfully even if the index doesn’t exist. This is useful for cleanup scripts that may run multiple times or when you’re unsure if an index exists.
Without the IF EXISTS clause, attempting to drop a non-existent index will raise an error.
// Standard syntax
await ditto.store.execute(query: "DROP INDEX color_idx ON cars")

// Alternative syntax
await ditto.store.execute(query: "DROP INDEX cars.color_idx")

// With IF EXISTS - safe for repeated execution
await ditto.store.execute(query: "DROP INDEX IF EXISTS color_idx ON cars")
// No error even if index doesn't exist

​
Controlling Index Usage

DQL provides several ways to control index selection in your queries.
Using index directives on a sync subscription will result in an invalid query error.

​
USE INDEX Syntax

The USE INDEX clause is the simple way to force a query to use a specific index. It appears after the collection name in SELECT statements:
DQL
SELECT * FROM collection_name USE INDEX 'index_name' WHERE ...
DQL
-- Force use of a specific index
SELECT * FROM cars USE INDEX 'color_idx' WHERE color = 'blue'
This syntax is straightforward and recommended for most use cases where you need to specify which index to use for a query.

​
When to Use USE INDEX

There are scenarios where you might want to force a specific index rather than letting DQL auto-select.
  1. Forcing a specific index when multiple could apply
DQL
-- You have indexes: customer_idx and total_idx
-- Force the customer index when you know it's more selective
SELECT * FROM orders USE INDEX 'customer_idx' 
WHERE customer_id = 'abc123' AND total > 1000
-- Without forcing, DQL might choose total_idx if it estimates incorrectly
  1. Preferring sort order optimization
DQL
-- You have indexes: date_idx and status_idx
-- Force the date index when sort performance is critical
SELECT * FROM events USE INDEX 'date_idx' 
WHERE status = 'active' ORDER BY date DESC LIMIT 10
  1. Working around index statistics
DQL
-- Force an index when you know the data distribution better than the optimizer
-- e.g., most cars are 'available' but you're querying a rare status
SELECT * FROM cars USE INDEX 'status_idx' 
WHERE status = 'maintenance' -- Only 0.1% of records
  1. Testing and performance comparison
DQL
-- Compare performance between different indexes
SELECT * FROM products USE INDEX 'category_idx' WHERE category = 'electronics'
-- vs
SELECT * FROM products USE INDEX 'category_brand_idx' WHERE category = 'electronics'

​
Advanced: USE DIRECTIVES Syntax

For more advanced scenarios, DQL also supports the USE DIRECTIVES syntax which allows fine-grained control over query optimization using JSON directives:
DQL
SELECT * FROM collection_name USE DIRECTIVES '<json-directives>' WHERE ...
DQL
-- Force use of a specific index (equivalent to USE INDEX)
SELECT * FROM cars USE DIRECTIVES '{"#index":"color_idx"}' 
WHERE color = 'blue'

-- Force collection scan (skip all indexes)
SELECT * FROM cars USE DIRECTIVES '{"#index":null}' 
WHERE color = 'blue'

-- Let DQL auto-select from available indexes
SELECT * FROM cars USE DIRECTIVES '{"#index":[]}' 
WHERE color = 'blue'

-- Prefer ordering optimization
SELECT * FROM cars USE DIRECTIVES '{"#prefer_order":true}' 
WHERE year > 2020 ORDER BY price DESC

// Using USE INDEX syntax
let result = await ditto.store.execute(
  query: "SELECT * FROM cars USE INDEX 'color_idx' WHERE color = :color",
  arguments: ["color": "blue"])

// Using USE DIRECTIVES syntax (advanced)
let result2 = await ditto.store.execute(
  query: "SELECT * FROM cars USE DIRECTIVES '{\"#index\":\"color_idx\"}' WHERE color = :color",
  arguments: ["color": "blue"])

// Using comment directive (advanced)
let result3 = await ditto.store.execute(
  query: "/*+ {\"cars\":{\"#index\":\"color_idx\"}} */ SELECT * FROM cars WHERE color = :color",
  arguments: ["color": "blue"])

​
Comment Directives

Comment directives provide the same functionality using special comments:
DQL
/*+ <json-directives> */ SELECT ...
-- or
--+ <json-directives>
SELECT ...
The JSON structure for comment directives supports both collection-specific and global settings:
DQL
-- Collection-specific directive
/*+ {"cars":{"#index":"color_idx"}} */ 
SELECT * FROM cars WHERE color = 'blue'

-- Global directive affecting all collections
/*+ {"#prefer_order":true} */ 
SELECT * FROM cars WHERE year > 2020 ORDER BY price DESC

-- Multiple directives
/*+ {
  "cars": {
    "#index": "color_idx",
    "#prefer_order": true
  },
  "#disable_index_scan": false
} */ 
SELECT * FROM cars WHERE color = 'blue' ORDER BY year

​
Directive Reference

Collection-specific directives always override global directives. Unrecognized directives are silently ignored, but the JSON must be valid or a parser error will occur.
DirectiveTypeDefaultDescription
#indexstring, array, or nullauto-selectSpecifies which index to use
#prefer_orderbooleanfalsePrefer indexes that optimize ORDER BY over WHERE
#disable_index_scanbooleanfalseDisable all index scans (global only)
The #index directive accepts different value types.
ValueBehavior
"index_name"Use the specified index if available
null or ""Force collection scan (no indexes)
[]Auto-select from available indexes
Given multiple directives, the last instance of any directive applies. For example, the following will result in ix2 being used and not ix1. 
SELECT ... FROM cars 
USE DIRECTIVES '{"#index":"ix1"}' 
WHERE ... /*+{"cars":{"#index":"ix2"}}*/

​
Ditto Instance Initialization

When initializing your Ditto instance, you may want to create indexes that are essential for your application’s performance. Since indexes are stored in the database and persist across application restarts, you need to handle cases where indexes might already exist from previous runs.

​
Initialization Patterns

There are three approaches for handling index creation during initialization:
  1. Use IF NOT EXISTS clause - The simplest and most efficient approach (recommended)
  2. Check before creating - Query system:indexes to see if the index exists
  3. Create and catch errors - Attempt to create the index and handle duplicate errors gracefully

​
Initialization Examples

// Approach 1: Use IF NOT EXISTS (recommended)
func initializeIndexes() async throws {
    let indexQueries = [
        "CREATE INDEX IF NOT EXISTS color_idx ON cars (color)",
        "CREATE INDEX IF NOT EXISTS name_idx ON users (name)",
        "CREATE INDEX IF NOT EXISTS date_idx ON orders (created_date)"
    ]
    
    for query in indexQueries {
        await ditto.store.execute(query: query)
        print("Index created or already exists")
    }
}

// Approach 2: Check if index exists before creating
func initializeIndexesWithCheck() async throws {
    let requiredIndexes = [
        ("cars", "color_idx", "color"),
        ("users", "name_idx", "name"),
        ("orders", "date_idx", "created_date")
    ]
    
    for (collection, indexName, field) in requiredIndexes {
        let existingIndex = await ditto.store.execute(
            query: "SELECT * FROM system:indexes WHERE collection = :collection AND _id = :indexId",
            arguments: ["collection": collection, "indexId": "\(collection).\(indexName)"])
        
        if existingIndex.items.isEmpty {
            await ditto.store.execute(
                query: "CREATE INDEX \(indexName) ON \(collection) (\(field))")
            print("Created index \(indexName) on \(collection)")
        } else {
            print("Index \(indexName) already exists on \(collection)")
        }
    }
}

// Approach 3: Create and handle errors
func initializeIndexesWithErrorHandling() async {
    let indexQueries = [
        "CREATE INDEX color_idx ON cars (color)",
        "CREATE INDEX name_idx ON users (name)",
        "CREATE INDEX date_idx ON orders (created_date)"
    ]
    
    for query in indexQueries {
        do {
            try await ditto.store.execute(query: query)
            print("Successfully created index")
        } catch {
            if error.localizedDescription.contains("already exists") {
                print("Index already exists, continuing...")
            } else {
                print("Failed to create index: \(error)")
                throw error
            }
        }
    }
}

​
Best Practices for Initialization

  1. Use IF NOT EXISTS for simplicity - The simplest and most efficient approach for most use cases
  2. Use the check-first approach for critical indexes - When you need detailed logging and control
  3. Batch index creation - Create all required indexes during initialization rather than on-demand
  4. Log index status - Always log whether indexes were created or already existed for debugging
  5. Handle errors gracefully - Don’t let index creation failures prevent your app from starting unless the indexes are absolutely critical
  6. Consider startup performance - The IF NOT EXISTS approach is fastest as it requires no additional queries

​
Error Handling

When working with indexes, you may encounter errors if an index with the same name already exists on a collection. This section covers how to handle such scenarios.

​
Duplicate Index Error

If you attempt to create an index with a name that already exists on the same collection, DQL will throw the following error: 
Database error: an index with the given name already exists on the given collection
To handle this error, you have several options:
  1. Use IF NOT EXISTS clause - Prevents the error entirely (recommended)
  2. Check if the index exists first by querying system:indexes
  3. Drop the existing index before creating a new one (use DROP INDEX IF EXISTS for safety)
  4. Use a different index name
  5. Catch and handle the error in your application code

​
Error Handling Examples

// Approach 1: Use IF NOT EXISTS (recommended)
await ditto.store.execute(query: "CREATE INDEX IF NOT EXISTS color_idx ON cars (color)")
// No error handling needed - succeeds whether index exists or not

// Approach 2: Check if index exists before creating
let existingIndexes = await ditto.store.execute(
  query: "SELECT * FROM system:indexes WHERE collection = :collection AND _id = :indexName",
  arguments: ["collection": "cars", "indexName": "cars.color_idx"])

if existingIndexes.items.isEmpty {
  // Safe to create the index
  await ditto.store.execute(query: "CREATE INDEX color_idx ON cars (color)")
}

// Approach 3: Handle the error
do {
  try await ditto.store.execute(query: "CREATE INDEX color_idx ON cars (color)")
} catch {
  if error.localizedDescription.contains("already exists") {
    print("Index already exists, skipping creation")
  } else {
    throw error
  }
}

​
Best Practices for Error Handling

  1. Use idempotent index creation: Check if an index exists before attempting to create it
  2. Implement retry logic: For transient errors, implement appropriate retry mechanisms
  3. Log errors appropriately: Distinguish between expected errors (like duplicate indexes) and unexpected ones
  4. Consider using unique naming conventions: Include timestamps or version numbers in index names when appropriate

​
How it works

​
General Behavior

  • When an index is added to a collection, that collection must be scanned and indexed, which may take time for large collections
  • Index directives only suggest which index to consider. DQL still requires a valid reason (WHERE or ORDER BY clause) to actually use the index. An index won’t be used if the query doesn’t benefit from it.

​
Persistence

  • SDK Indexes are retained when Ditto shuts down
  • Index names are unique to a collection - creating an index with an existing name in the same collection will raise an error
  • To replace an existing index, you must first drop it, then recreate it

​
Missing Fields

  • SDK - Documents that don’t contain the indexed field will be included in index-optimized query results. (i.e. INCLUDE MISSING is implied).
  • Big Peer HTTP API - By default, documents that don’t contain the indexed field will not be included in index-optimized query results. (i.e. EXCLUDE MISSING)

​
Supported APIs

API/FeatureIndex Support
execute SDK API✅ Supported
registerObserver SDK API✅ Supported
registerSubscription SDK API❌ Not Supported
Big Peer HTTP API❌ Not Supported
SDKs with in-memory storage❌ Not Supported

​
Best Practices

  1. Create indexes on frequently queried fields: Focus on fields that appear often in your WHERE clauses
  2. Monitor index usage: Use the system:indexes collection to track your indexes
  3. Consider index creation cost: Adding indexes to large collections requires scanning and indexing time
  4. Clean up unused indexes: Drop indexes that are no longer needed to save storage space and improve write performance

​
Roadmap

DQL indexing focuses on core functionality for optimal performance. The following features are not currently supported:
  • Composite indexes on multiple fields
  • Partial indexes (e.g., CREATE INDEX ... 'WHERE status = 'active')
  • Functional indexes (e.g., UPPER(field1))
  • Index optimization for queries with OR / AND clauses
  • Tombstone indexing
  • Custom indexing on the Big Peer
  • Array & Object support
Future releases may expand indexing capabilities based on user feedback and performance requirements.