Indexing System

Master ObjectQuel's indexing system to optimize database performance through annotation-driven index management, automated migration generation, and intelligent query optimization strategies.

Index Fundamentals in ObjectQuel

ObjectQuel's indexing system allows you to define database indexes directly in your entity classes using annotations. These annotations are automatically processed during migrations to create, modify, or remove indexes in your database:

<?php
namespace App\Entity;

use Quellabs\ObjectQuel\Annotations\Orm;

/**
 * @Orm\Table(name="products")
 * @Orm\Index(name="idx_product_search", columns={"name", "description"})
 * @Orm\Index(name="idx_price_range", columns={"price", "category_id"})
 * @Orm\UniqueIndex(name="idx_unique_sku", columns={"sku"})
 */
class ProductEntity {

    /**
     * @Orm\Column(name="product_id", type="integer", primary_key=true)
     * @Orm\PrimaryKeyStrategy(strategy="identity")
     */
    private ?int $productId = null;

    /**
     * @Orm\Column(name="name", type="string", limit=255)
     */
    private string $name;

    /**
     * @Orm\Column(name="sku", type="string", limit=50)
     */
    private string $sku;

    /**
     * @Orm\Column(name="price", type="decimal", limit="10,2")
     */
    private float $price;

    /**
     * @Orm\Column(name="description", type="text", nullable=true)
     */
    private ?string $description = null;

    /**
     * @Orm\Column(name="category_id", type="integer")
     */
    private int $categoryId;
}

Key Benefits:

• Define indexes alongside entity definitions for clear documentation
• Automatic migration generation for index changes
• Type safety and IDE support for index definitions
• Version control friendly - indexes are tracked with your code

Regular Indexes with @Orm\Index

Regular indexes improve query performance for columns frequently used in WHERE clauses, JOIN conditions, or ORDER BY operations:

Single Column Indexes

/**
 * @Orm\Table(name="users")
 * @Orm\Index(name="idx_email", columns={"email"})
 * @Orm\Index(name="idx_status", columns={"status"})
 * @Orm\Index(name="idx_created", columns={"created_at"})
 */
class UserEntity {

    /**
     * @Orm\Column(name="email", type="string", limit=255)
     */
    private string $email;

    /**
     * @Orm\Column(name="status", type="string", limit=20)
     */
    private string $status;

    /**
     * @Orm\Column(name="created_at", type="datetime")
     */
    private \DateTime $createdAt;
}

Composite Indexes

/**
 * @Orm\Table(name="orders")
 * @Orm\Index(name="idx_customer_date", columns={"customer_id", "order_date"})
 * @Orm\Index(name="idx_status_priority", columns={"status", "priority", "created_at"})
 */
class OrderEntity {

    /**
     * @Orm\Column(name="customer_id", type="integer")
     */
    private int $customerId;

    /**
     * @Orm\Column(name="order_date", type="date")
     */
    private \DateTime $orderDate;

    /**
     * @Orm\Column(name="status", type="string", limit=20)
     */
    private string $status;

    /**
     * @Orm\Column(name="priority", type="integer")
     */
    private int $priority;
}

Composite Index Benefits:

• Optimize queries filtering on multiple columns
• Column order matters - most selective columns should come first
• Can satisfy queries using any left prefix of the index
• Reduce the need for multiple single-column indexes

Unique Indexes with @Orm\UniqueIndex

Unique indexes enforce data integrity by preventing duplicate values while also providing performance benefits:

Single Column Unique Constraints

/**
 * @Orm\Table(name="users")
 * @Orm\UniqueIndex(name="idx_unique_email", columns={"email"})
 * @Orm\UniqueIndex(name="idx_unique_username", columns={"username"})
 */
class UserEntity {

    /**
     * @Orm\Column(name="email", type="string", limit=255)
     */
    private string $email;

    /**
     * @Orm\Column(name="username", type="string", limit=50)
     */
    private string $username;
}

Composite Unique Constraints

/**
 * @Orm\Table(name="product_reviews")
 * @Orm\UniqueIndex(name="idx_unique_user_product", columns={"user_id", "product_id"})
 */
class ProductReviewEntity {

    /**
     * @Orm\Column(name="user_id", type="integer")
     */
    private int $userId;

    /**
     * @Orm\Column(name="product_id", type="integer")
     */
    private int $productId;

    // Ensures one review per user per product
}

Index Annotation Parameters

Both @Orm\Index and @Orm\UniqueIndex support the same parameters:

/**
 * @Orm\Index(name="idx_search_products", columns={"category_id", "name", "status"})
 * @Orm\UniqueIndex(name="idx_unique_product_code", columns={"company_id", "product_code"})
 */

Strategic Index Design

Query-Based Index Planning

// Consider these common query patterns:

// 1. Simple filtering - needs single column index
$products = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.status = :status
", ['status' => 'active']);

// 2. Multi-column filtering - needs composite index
$products = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.category_id = :categoryId and p.price > :minPrice
    sort by p.name asc
", ['categoryId' => 1, 'minPrice' => 10.00]);

// 3. Range queries with sorting - needs covering index
$products = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.created_at >= :startDate
    sort by p.created_at desc, p.name asc
", ['startDate' => '2024-01-01']);

/**
 * Optimal indexes for above queries:
 * @Orm\Index(name="idx_status", columns={"status"})
 * @Orm\Index(name="idx_category_price_name", columns={"category_id", "price", "name"})
 * @Orm\Index(name="idx_created_name", columns={"created_at", "name"})
 */

Index Design Best Practices

1. Selectivity-Based Column Ordering

/**
 * Good: Most selective column first
 * @Orm\Index(name="idx_optimal", columns={"user_id", "status", "created_at"})
 *
 * Poor: Less selective column first
 * @Orm\Index(name="idx_suboptimal", columns={"status", "user_id", "created_at"})
 */
class OrderEntity {
    private int $userId;        // High selectivity (unique per user)
    private string $status;     // Low selectivity (few distinct values)
    private \DateTime $createdAt; // Medium selectivity
}

2. Covering Indexes for Performance

/**
 * Covering index includes all columns needed by query
 * @Orm\Index(name="idx_product_search_covering", columns={"category_id", "status", "name", "price"})
 */
class ProductEntity {
    // Query can be satisfied entirely from index without table lookup
    // SELECT name, price FROM products WHERE category_id = ? AND status = ?
}

Migration Integration

ObjectQuel automatically generates migrations for index changes when you run the sculpt tool:

Generating Index Migrations

# Analyze entity annotations and generate migrations
php bin/sculpt make:migrations

# Apply migrations to update database schema
php bin/sculpt quel:migrate

Generated Migration Example

<?php
use Phinx\Migration\AbstractMigration;

class AddProductIndexes20250603123456 extends AbstractMigration {

    public function up(): void {
        $this->table('products')
            ->addIndex(['name', 'category_id'], [
                'name' => 'idx_product_search',
                'unique' => false,
            ])
            ->addIndex(['sku'], [
                'name' => 'idx_unique_sku',
                'unique' => true,
            ])
            ->save();
    }

    public function down(): void {
        $this->table('products')
            ->removeIndex(['name', 'category_id'])
            ->removeIndex(['sku'])
            ->save();
    }
}