Indexing

Velocity provides comprehensive support for database indexes, which improve query performance by allowing faster data retrieval.

Overview

Indexes are database structures that speed up data retrieval operations on tables. They work like a book's index—allowing the database to find rows quickly without scanning the entire table. While indexes improve query performance, they add overhead to INSERT, UPDATE, and DELETE operations as the index must be maintained.

Creating Indexes

Indexes are created on tables using the CreateIndex() method:

var schema = new Schema("myschema");
var usersTable = schema.CreateTable("users");
usersTable.CreateColumn("id", DataType.Integer);
usersTable.CreatePrimaryKey("pk_users", "id");
usersTable.CreateColumn("email", DataType.Varchar, size: 255);
usersTable.CreateColumn("username", DataType.Varchar, size: 100);
usersTable.CreateColumn("created_at", DataType.DateTime);

// Create single-column index
var emailIndex = usersTable.CreateIndex("idx_email", "email", IndexOrderType.Ascending);

using (var manager = new Manager(connection))
{
    manager.BuildSchema(schema);
}

Single-Column Indexes

Single-column indexes are the simplest form, indexing one column:

// Index in ascending order (default for most queries)
var usernameIndex = usersTable.CreateIndex(
    name: "idx_username",
    columnName: "username",
    orderType: IndexOrderType.Ascending
);

// Index in descending order (useful for sorting newest-first)
var dateIndex = usersTable.CreateIndex(
    name: "idx_created_desc",
    columnName: "created_at",
    orderType: IndexOrderType.Descending
);

When to Use Single-Column Indexes

Frequent WHERE clauses: Columns commonly used in WHERE conditions
JOIN columns: Foreign key columns used in table joins
ORDER BY columns: Columns frequently used for sorting
High cardinality: Columns with many unique values (email, username)

Composite Indexes

Composite indexes span multiple columns and are useful for queries that filter or sort by multiple columns:

var ordersTable = schema.CreateTable("orders");
ordersTable.CreateColumn("id", DataType.Integer);
ordersTable.CreatePrimaryKey("pk_orders", "id");
ordersTable.CreateColumn("user_id", DataType.Integer);
ordersTable.CreateColumn("status", DataType.Varchar, size: 50);
ordersTable.CreateColumn("created_at", DataType.DateTime);

// Create composite index on user_id and status
var userStatusIndex = ordersTable.CreateIndex("idx_user_status");
userStatusIndex.AddIndexColumn("user_id", IndexOrderType.Ascending);
userStatusIndex.AddIndexColumn("status", IndexOrderType.Ascending);

Column Order Matters

The order of columns in a composite index is significant:

// Index optimized for: WHERE user_id = ? AND status = ?
// Also works for: WHERE user_id = ?
var index1 = ordersTable.CreateIndex("idx_user_status");
index1.AddIndexColumn("user_id", IndexOrderType.Ascending);
index1.AddIndexColumn("status", IndexOrderType.Ascending);

// Different index: WHERE status = ? AND user_id = ?
// Also works for: WHERE status = ?
var index2 = ordersTable.CreateIndex("idx_status_user");
index2.AddIndexColumn("status", IndexOrderType.Ascending);
index2.AddIndexColumn("user_id", IndexOrderType.Ascending);

Rule of thumb: Place the most selective (most unique values) or most frequently queried column first.

Mixed Sort Orders

Composite indexes can have different sort orders per column:

// Optimized for: ORDER BY status ASC, created_at DESC
var sortIndex = ordersTable.CreateIndex("idx_status_date");
sortIndex.AddIndexColumn("status", IndexOrderType.Ascending);
sortIndex.AddIndexColumn("created_at", IndexOrderType.Descending);

// This index benefits queries like:
// SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC

Index Naming Conventions

While you can name indexes anything, following conventions improves maintainability:

// Prefix with "idx_" for regular indexes
var emailIdx = table.CreateIndex("idx_email", "email", IndexOrderType.Ascending);

// Include column names in composite indexes
var compositeIdx = table.CreateIndex("idx_user_status");
compositeIdx.AddIndexColumn("user_id", IndexOrderType.Ascending);
compositeIdx.AddIndexColumn("status", IndexOrderType.Ascending);

// Include sort direction if descending
var descIdx = table.CreateIndex("idx_created_desc", "created_at", IndexOrderType.Descending);

Managing Indexes

Creating Indexes Standalone

Indexes can be created after a table exists:

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
    var usersTable = schema["users"];

    // Create a new index
    var newIndex = usersTable.CreateIndex("idx_last_login", "last_login_at", IndexOrderType.Descending);

    // Apply the index to the database
    manager.CreateIndex(newIndex);
}

Dropping Indexes

Remove indexes that are no longer needed:

using (var manager = new Manager(connection))
{
    var schema = manager.LoadSchema("myschema");
    var usersTable = schema["users"];
    var index = usersTable.Indexes["idx_email"];

    manager.DropIndex(index);
}

Performance Considerations

Benefits of Indexes

Faster queries: Dramatically speed up SELECT queries with WHERE, JOIN, or ORDER BY
Unique constraints: Enforce uniqueness (though unique constraints are separate)
Covering queries: Queries reading only indexed columns can be satisfied entirely from the index

Costs of Indexes

Storage space: Indexes consume additional disk space
Write overhead: INSERT, UPDATE, and DELETE operations must update indexes
Maintenance: Indexes may require periodic rebuilding or statistics updates

Index Selection Guidelines

// ✓ Good: Index frequently queried columns
var emailIdx = table.CreateIndex("idx_email", "email", IndexOrderType.Ascending);

// ✓ Good: Index foreign keys for JOIN performance
var userIdIdx = table.CreateIndex("idx_user_id", "user_id", IndexOrderType.Ascending);

// ✗ Avoid: Indexing low-cardinality columns
// (e.g., boolean, gender, status with few values - poor selectivity)
var activeIdx = table.CreateIndex("idx_is_active", "is_active", IndexOrderType.Ascending);

// ✗ Avoid: Over-indexing (too many indexes on one table)
// Each index slows down writes and consumes space

Common Patterns

Foreign Key Indexes

Always index foreign key columns for JOIN performance:

var ordersTable = schema.CreateTable("orders");
ordersTable.CreateColumn("id", DataType.Integer);
ordersTable.CreatePrimaryKey("pk_orders", "id");
ordersTable.CreateColumn("user_id", DataType.Integer);

// Index the foreign key
var userIdIndex = ordersTable.CreateIndex("idx_user_id", "user_id", IndexOrderType.Ascending);

Timestamp Indexes

Index timestamp columns for time-based queries:

var logsTable = schema.CreateTable("logs");
logsTable.CreateColumn("id", DataType.Integer);
logsTable.CreateColumn("created_at", DataType.DateTime);
logsTable.CreateColumn("level", DataType.Varchar, size: 20);

// Index for queries like: WHERE created_at > ?
var timeIndex = logsTable.CreateIndex("idx_created", "created_at", IndexOrderType.Descending);

// Composite index for: WHERE level = ? AND created_at > ?
var levelTimeIndex = logsTable.CreateIndex("idx_level_created");
levelTimeIndex.AddIndexColumn("level", IndexOrderType.Ascending);
levelTimeIndex.AddIndexColumn("created_at", IndexOrderType.Descending);

Covering Indexes

Indexes that include all columns needed by a query (covering indexes):

var productsTable = schema.CreateTable("products");
productsTable.CreateColumn("id", DataType.Integer);
productsTable.CreateColumn("category_id", DataType.Integer);
productsTable.CreateColumn("name", DataType.Varchar, size: 200);
productsTable.CreateColumn("price", DataType.Decimal, precision: 10, scale: 2);

// Covering index for: SELECT name, price WHERE category_id = ?
var coveringIndex = productsTable.CreateIndex("idx_category_covering");
coveringIndex.AddIndexColumn("category_id", IndexOrderType.Ascending);
coveringIndex.AddIndexColumn("name", IndexOrderType.Ascending);
coveringIndex.AddIndexColumn("price", IndexOrderType.Ascending);

Database-Specific Behavior

While Velocity abstracts index creation, databases implement indexes differently:

DB2: Supports various index types including clustering indexes
MySQL: Supports B-tree and Hash indexes (storage engine dependent)
Oracle: Supports B-tree, bitmap, and function-based indexes
PostgreSQL: Supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN indexes
SQL Server: Supports clustered (one per table) and non-clustered indexes
SQLite: Supports B-tree indexes only
Teradata: Uses a hash-based Primary Index (set automatically on the auto-increment column) to distribute rows across AMPs. Regular secondary indexes are created with CREATE INDEX for query optimisation. Column sort direction is not applied to Teradata index definitions.

Velocity creates B-tree indexes by default, which work well for most use cases across all databases.

Example: E-Commerce Schema

Complete example with indexes for an e-commerce application:

using YndigoBlue.Velocity;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Engine;

var schema = new Schema("ecommerce");

// Users table
var usersTable = schema.CreateTable("users");
usersTable.CreateColumn("id", DataType.Integer, autoGenerate: true);
usersTable.CreatePrimaryKey("pk_users", "id");
usersTable.CreateColumn("email", DataType.Varchar, size: 255, notNull: true);
usersTable.CreateColumn("username", DataType.Varchar, size: 100, notNull: true);
usersTable.CreateColumn("created_at", DataType.DateTime);
usersTable.CreateIndex("idx_email", "email", IndexOrderType.Ascending);
usersTable.CreateIndex("idx_username", "username", IndexOrderType.Ascending);

// Products table
var productsTable = schema.CreateTable("products");
productsTable.CreateColumn("id", DataType.Integer, autoGenerate: true);
productsTable.CreatePrimaryKey("pk_products", "id");
productsTable.CreateColumn("category_id", DataType.Integer);
productsTable.CreateColumn("name", DataType.Varchar, size: 200);
productsTable.CreateColumn("price", DataType.Decimal, precision: 10, scale: 2);
productsTable.CreateColumn("stock", DataType.Integer);
productsTable.CreateColumn("created_at", DataType.DateTime);

// Index for category browsing
productsTable.CreateIndex("idx_category", "category_id", IndexOrderType.Ascending);

// Composite index for category + price sorting
var categoryPriceIdx = productsTable.CreateIndex("idx_category_price");
categoryPriceIdx.AddIndexColumn("category_id", IndexOrderType.Ascending);
categoryPriceIdx.AddIndexColumn("price", IndexOrderType.Ascending);

// Orders table
var ordersTable = schema.CreateTable("orders");
ordersTable.CreateColumn("id", DataType.Integer, autoGenerate: true);
ordersTable.CreatePrimaryKey("pk_orders", "id");
ordersTable.CreateColumn("user_id", DataType.Integer, notNull: true);
ordersTable.CreateColumn("status", DataType.Varchar, size: 50);
ordersTable.CreateColumn("total", DataType.Decimal, precision: 10, scale: 2);
ordersTable.CreateColumn("created_at", DataType.DateTime);

// Foreign key index
ordersTable.CreateIndex("idx_user_id", "user_id", IndexOrderType.Ascending);

// Composite index for user's order history
var userOrdersIdx = ordersTable.CreateIndex("idx_user_created");
userOrdersIdx.AddIndexColumn("user_id", IndexOrderType.Ascending);
userOrdersIdx.AddIndexColumn("created_at", IndexOrderType.Descending);

// Index for admin order management
var statusDateIdx = ordersTable.CreateIndex("idx_status_created");
statusDateIdx.AddIndexColumn("status", IndexOrderType.Ascending);
statusDateIdx.AddIndexColumn("created_at", IndexOrderType.Descending);

// Build the schema
using (var manager = new Manager(connection))
{
    manager.BuildSchema(schema, overwrite: true);
}

Best Practices

Index selectively: Don't index every column—focus on frequently queried columns
Index foreign keys: Always index columns used in JOINs
Consider composite indexes: Multiple columns often queried together
Monitor query performance: Use database query plans to identify missing indexes
Column order matters: Most selective column first in composite indexes
Avoid over-indexing: Each index slows down writes and consumes storage
Index cardinality: High-cardinality (many unique values) columns benefit most
Review periodically: Drop unused indexes to improve write performance

Limitations

Index names must be unique within a table
Not all column types can be indexed (BLOB, CLOB types may have restrictions)
Some databases limit the number of columns in a composite index
Maximum index key size varies by database
Index rebuilding or statistics updates may be needed for optimal performance

Table of Contents