This feature is available in SDK version 4.12.0 and later.
Index Operations Overview
DQL supports the following index operations:Operation | Description |
---|---|
CREATE INDEX | Creates a simple on a specified field |
DROP INDEX | Removes an existing index |
USE INDEX | Force a query to use a specific index |
USE DIRECTIVES | Advanced control over index usage and query optimization |
SELECT from system:indexes | Lists 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
color
field in the cars
collection:
DQL
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.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
LIKE
Indexes can also accelerate LIKE queries when searching for prefix patterns:DQL
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
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 specialsystem:indexes
collection:
DQL
system:indexes
collection follows this schema:
Dropping Indexes
Remove an index that is no longer needed:DQL
color_idx
index from the cars
collection:
DQL
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.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
TheUSE 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
DQL
When to Use USE INDEX
There are scenarios where you might want to force a specific index rather than letting DQL auto-select.- Forcing a specific index when multiple could apply
DQL
- Preferring sort order optimization
DQL
- Working around index statistics
DQL
- Testing and performance comparison
DQL
Advanced: USE DIRECTIVES Syntax
For more advanced scenarios, DQL also supports theUSE DIRECTIVES
syntax which allows fine-grained control over query optimization using JSON directives:
DQL
DQL
Comment Directives
Comment directives provide the same functionality using special comments:DQL
DQL
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.Directive | Type | Default | Description |
---|---|---|---|
#index | string, array, or null | auto-select | Specifies which index to use |
#prefer_order | boolean | false | Prefer indexes that optimize ORDER BY over WHERE |
#disable_index_scan | boolean | false | Disable all index scans (global only) |
#intersects | Integer | 2 | Maximum number of index scans to intersect in a single operator |
#max_scans | Integer | 5 | Maximum number of scans to use in a plan |
#max_inlist | Integer | unlimited | Maximum number of elements in the list when considering conversion to logical OR terms |
#index
directive accepts different value types.
Value | Behavior |
---|---|
"index_name" | Use the specified index if available |
null or "" | Force collection scan (no indexes) |
[] | Auto-select from available indexes |
ix2
being used and not ix1
.
#max_inlist
and the remaining number of scans below #max_scans
at the time the list is processed. #max_inlist
should be used to limit the number of scans used for processing of IN-list filters when it is preferable to apply other filters.
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:- Use IF NOT EXISTS clause - The simplest and most efficient approach (recommended)
- Check before creating - Query
system:indexes
to see if the index exists - Create and catch errors - Attempt to create the index and handle duplicate errors gracefully
Initialization Examples
Best Practices for Initialization
- Use IF NOT EXISTS for simplicity - The simplest and most efficient approach for most use cases
- Use the check-first approach for critical indexes - When you need detailed logging and control
- Batch index creation - Create all required indexes during initialization rather than on-demand
- Log index status - Always log whether indexes were created or already existed for debugging
- Handle errors gracefully - Don’t let index creation failures prevent your app from starting unless the indexes are absolutely critical
- 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:- Use IF NOT EXISTS clause - Prevents the error entirely (recommended)
- Check if the index exists first by querying
system:indexes
- Drop the existing index before creating a new one (use
DROP INDEX IF EXISTS
for safety) - Use a different index name
- Catch and handle the error in your application code
Error Handling Examples
Best Practices for Error Handling
- Use idempotent index creation: Check if an index exists before attempting to create it
- Implement retry logic: For transient errors, implement appropriate retry mechanisms
- Log errors appropriately: Distinguish between expected errors (like duplicate indexes) and unexpected ones
- 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).
- Ditto Server 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/Feature | Index Support |
---|---|
execute SDK API | ✅ Supported |
registerObserver SDK API | ✅ Supported |
registerSubscription SDK API | ❌ Not Supported |
Ditto Server HTTP API | ❌ Not Supported |
SDKs with in-memory storage | ❌ Not Supported |
Best Practices
- Create indexes on frequently queried fields: Focus on fields that appear often in your WHERE clauses
- Monitor index usage: Use the
system:indexes
collection to track your indexes - Consider index creation cost: Adding indexes to large collections requires scanning and indexing time
- 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 Ditto Server
- Array & Object support
Future releases may expand indexing capabilities based on user feedback and performance requirements.