Indexes

Indexes dramatically improve query performance by allowing the database to quickly locate documents without scanning the entire collection.

Index basics

Why use indexes?

Without an index, jasonisnthappy must scan every document to find matches. With an index:

Queries on indexed fields are significantly faster
Unique indexes prevent duplicate values
Compound indexes support multi-field queries
Range queries benefit from B-tree structure

Indexes speed up reads but add overhead to writes. Only index fields you query frequently.

Creating indexes

Single-field index

Create an index on a single field.

use jasonisnthappy::Database;

let db = Database::open("my.db")?;

// Create non-unique index on age field
db.create_index(
    "users",        // collection name
    "age_idx",      // index name
    "age",          // field name
    false           // unique: false
)?;

println!("Index created successfully");

Index names must be unique within a collection. Use descriptive names like field_idx or field_unique_idx.

Unique index

Enforce uniqueness constraints with unique indexes.

// Prevent duplicate emails
db.create_index(
    "users",
    "email_unique_idx",
    "email",
    true  // unique: true
)?;

// Attempting to insert duplicate email will fail
let users = db.collection("users");
users.insert(json!({"email": "alice@example.com"}))?;  // OK
users.insert(json!({"email": "alice@example.com"}))?;  // Error!

Unique indexes are built on existing data during creation. If duplicates exist, index creation fails.

Compound indexes

Create indexes on multiple fields for multi-criteria queries.

// Index on city AND age (in that order)
db.create_compound_index(
    "users",
    "city_age_idx",
    &["city", "age"],  // field order matters!
    false
)?;

Leftmost prefix rule
Field order
Unique compound

Compound indexes follow the leftmost prefix rule:

// Index: ["city", "age"]

// ✅ Uses index: queries on "city" alone
users.find("city is \"NYC\"")?;

// ✅ Uses index: queries on "city" and "age"
users.find("city is \"NYC\" and age > 25")?;

// ❌ Cannot use index: queries on "age" alone
users.find("age > 25")?;

The index can only be used if the query includes the leftmost field(s).

Choose field order based on query patterns:

// If you often query by city, then filter by age:
db.create_compound_index("users", "idx1", &["city", "age"], false)?;

// If you often query by age, then filter by city:
db.create_compound_index("users", "idx2", &["age", "city"], false)?;

// For different query patterns, create separate indexes

// Ensure combination of fields is unique
db.create_compound_index(
    "reservations",
    "room_date_unique",
    &["room_id", "date"],
    true  // unique combination
)?;

// Same room can't be reserved twice on the same date

Using indexes

Once created, indexes are used automatically by the query engine.

Index-optimized queries

// After creating index on "email"
db.create_index("users", "email_idx", "email", false)?;

let users = db.collection("users");

// This query automatically uses the email index
let results = users.find("email is \"alice@example.com\"")?;

// Range queries also benefit
let results = users.find("email >= \"a\" and email < \"b\"")?;

Compound index queries

// Create compound index
db.create_compound_index(
    "orders",
    "customer_status_idx",
    &["customer_id", "status"],
    false
)?;

let orders = db.collection("orders");

// ✅ Uses index: queries leftmost field
let results = orders.find("customer_id is \"cust_123\"")?;

// ✅ Uses index: queries both fields
let results = orders.find(
    "customer_id is \"cust_123\" and status is \"pending\""
)?;

// ❌ Cannot use index: missing leftmost field
let results = orders.find("status is \"pending\"")?;

Managing indexes

List indexes

View all indexes on a collection.

let indexes = db.list_indexes("users")?;

for index in indexes {
    println!("Index: {}", index.name);
    println!("  Fields: {:?}", index.fields);
    println!("  Unique: {}", index.unique);
    println!("  B-tree root: {}", index.btree_root);
}

Drop an index

Remove an index when it’s no longer needed.

db.drop_index("users", "old_idx")?;
println!("Index dropped");

Dropping an index is immediate and cannot be undone. Queries using that index will fall back to collection scans.

Index strategies

Equality queries

Identify frequently queried fields

// If you often query by email
let user = users.find("email is \"user@example.com\"")?;

Create a unique index

db.create_index("users", "email_idx", "email", true)?;

Enjoy fast lookups

Queries go from O(n) to O(log n) complexity.

Range queries

Indexes optimize range queries on sorted fields.

// Create index on numeric field
db.create_index("products", "price_idx", "price", false)?;

let products = db.collection("products");

// Fast range query using index
let results = products.find("price >= 10.00 and price <= 50.00")?;

Multi-field queries

Use compound indexes for queries with multiple conditions.

// Common query pattern: filter by status, then by date
db.create_compound_index(
    "tasks",
    "status_due_idx",
    &["status", "due_date"],
    false
)?;

let tasks = db.collection("tasks");

// Efficiently uses compound index
let results = tasks.find(
    "status is \"pending\" and due_date < \"2024-12-31\""
)?;

Covering indexes

If your query only needs indexed fields, use projection:

// Index on email
db.create_index("users", "email_idx", "email", false)?;

// Query that only returns indexed field
let results = users.query()
    .filter("email >= \"a\" and email < \"b\"")
    .project(&["email"])  // Only indexed field
    .execute()?;

Index performance

Write performance

Each index adds overhead to insert, update, and delete operations.

let users = db.collection("users");

// Fast: no index updates
users.insert(json!({"name": "Alice", "age": 30}))?;

Best practice: Only create indexes you actually use. Remove unused indexes to improve write performance.

Read performance

Indexes can improve read performance by orders of magnitude.

Collection size	Without index	With index
1,000 docs	0.5ms	0.01ms
10,000 docs	5ms	0.02ms
100,000 docs	50ms	0.03ms
1,000,000 docs	500ms	0.04ms

Times are approximate for equality queries

Best practices

Create indexes on:

Fields used in WHERE clauses (query filters)
Fields used for sorting
Fields used for uniqueness constraints
Foreign key-like fields (user_id, product_id, etc.)

Avoid indexing:

Fields that are rarely queried
Fields with very low cardinality (e.g., boolean fields)
Very large text fields (use text indexes instead)
Fields that change frequently if you rarely query them

Index naming conventions

// Good index names (descriptive and unique)
db.create_index("users", "email_unique_idx", "email", true)?;
db.create_index("users", "created_at_idx", "created_at", false)?;
db.create_compound_index("orders", "customer_date_idx", &["customer_id", "created_at"], false)?;

// Bad index names (vague, not descriptive)
db.create_index("users", "idx1", "email", true)?;  // What does idx1 mean?
db.create_index("users", "index", "age", false)?;   // Too generic

Index maintenance

Monitor index usage

Track which queries are slow in your application.

Create indexes for slow queries

db.create_index("users", "frequently_queried_field_idx", "field", false)?;

Remove unused indexes

db.drop_index("users", "rarely_used_idx")?;

Rebuild indexes after bulk operations

Indexes are automatically updated, but checkpoint after bulk inserts:

users.insert_many(vec![/* thousands of docs */])?;
db.checkpoint()?;  // Flush WAL to disk

Common patterns

User lookup by email

db.create_index("users", "email_unique_idx", "email", true)?;

let user = users.find_one("email is \"user@example.com\"")?;

Product search by category and price

db.create_compound_index(
    "products",
    "category_price_idx",
    &["category", "price"],
    false
)?;

let products = db.collection("products");
let results = products.find(
    "category is \"electronics\" and price < 500.00"
)?;

Unique username/email

db.create_index("users", "username_unique", "username", true)?;
db.create_index("users", "email_unique", "email", true)?;

// Prevents duplicates automatically
users.insert(json!({"username": "alice", "email": "alice@example.com"}))?;

Time-based queries

db.create_index("events", "timestamp_idx", "timestamp", false)?;

let events = db.collection("events");
let recent = events.find("timestamp > 1704067200")?;  // Unix timestamp

Next steps

Querying

Master the query language

Full-text search

Create text indexes for search

Performance

Optimize database performance

Schema validation

Validate document structure

Get Started

Core Concepts

Guides

Language SDKs

Index basics

Why use indexes?

Creating indexes

Single-field index

Unique index

Compound indexes

Using indexes

Index-optimized queries

Compound index queries

Managing indexes

List indexes

Drop an index

Index strategies

Equality queries

Range queries

Multi-field queries

Covering indexes

Index performance

Write performance

Read performance

Best practices

Index naming conventions

Index maintenance

Common patterns

User lookup by email

Product search by category and price

Unique username/email

Time-based queries

Next steps

Querying

Full-text search

Performance

Schema validation

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

Language SDKs

​Index basics

​Why use indexes?

​Creating indexes

​Single-field index

​Unique index

​Compound indexes

​Using indexes

​Index-optimized queries

​Compound index queries

​Managing indexes

​List indexes

​Drop an index

​Index strategies

​Equality queries

​Range queries

​Multi-field queries

​Covering indexes

​Index performance

​Write performance

​Read performance

​Best practices

​Index naming conventions

​Index maintenance

​Common patterns

​User lookup by email

​Product search by category and price

​Unique username/email

​Time-based queries

​Next steps

Querying

Full-text search

Performance

Schema validation

Build docs developers (and LLMs) love

Index basics

Why use indexes?

Creating indexes

Single-field index

Unique index

Compound indexes

Using indexes

Index-optimized queries

Compound index queries

Managing indexes

List indexes

Drop an index

Index strategies

Equality queries

Range queries

Multi-field queries

Covering indexes

Index performance

Write performance

Read performance

Best practices

Index naming conventions

Index maintenance

Common patterns

User lookup by email

Product search by category and price

Unique username/email

Time-based queries

Next steps