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:

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

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 properties 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={"createdAt"})
 */
class UserEntity { ... }

Composite Indexes

/**
 * @Orm\Table(name="orders")
 * @Orm\Index(name="idx_customer_date", columns={"customerId", "orderDate"})
 * @Orm\Index(name="idx_status_priority", columns={"status", "priority", "createdAt"})
 */
class OrderEntity { ... }

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:

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

Composite unique constraints work the same way and are useful for enforcing business rules spanning multiple columns:

/**
 * @Orm\Table(name="product_reviews")
 * @Orm\UniqueIndex(name="idx_unique_user_product", columns={"userId", "productId"})
 */
class ProductReviewEntity { ... }
// Ensures one review per user per product

Full-Text Indexes with @Orm\FullTextIndex

Full-text indexes enable natural language search across text columns, providing far more powerful and relevant results than LIKE '%keyword%' queries. They are supported on CHAR, VARCHAR, and TEXT columns.

/**
 * @Orm\Table(name="articles")
 * @Orm\FullTextIndex(name="idx_ft_article_content", columns={"title", "body"})
 */
class ArticleEntity { ... }

You can define separate full-text indexes for different search contexts, for example keeping a broad content index distinct from a name-only index for independent relevance ranking:

/**
 * @Orm\Table(name="products")
 * @Orm\FullTextIndex(name="idx_ft_product_name", columns={"name"})
 * @Orm\FullTextIndex(name="idx_ft_product_content", columns={"name", "description", "tags"})
 */
class ProductEntity { ... }

Querying Full-Text Indexes

ObjectQuel exposes full-text search through two functions: search() for filtering and search_score() for retrieving the relevance score. Both accept one or more field references followed by a string literal or :parameter as the search term.

// Filter results using search()
$articles = $entityManager->executeQuery("
    range of a is App\\Entity\\ArticleEntity
    retrieve (a) where search(a.title, a.body, :query)
", ['query' => 'database performance tuning']);

// Retrieve relevance score and sort by it using search_score()
$articles = $entityManager->executeQuery("
    range of a is App\\Entity\\ArticleEntity
    retrieve (a, search_score(a.title, a.body, :query) as score)
    where search(a.title, a.body, :query)
    sort by score desc
", ['query' => 'database performance tuning']);

Limitations to be Aware Of:

• Only supported on InnoDB and MyISAM storage engines in MySQL/MariaDB
• Words shorter than the ft_min_word_len server variable (default: 4) are ignored by the default parser
• Common stop words (e.g. "the", "is") are excluded from the index by the default parser

Index Annotation Parameters

All three annotation types share the same parameters:

Sculpt Commands

ObjectQuel provides several sculpt commands for managing indexes from the command line.

make:migrations

Analyzes entity annotations and generates a migration file for any index changes detected:

php bin/sculpt make:migrations

quel:migrate

Applies pending migrations to update the database schema:

php bin/sculpt quel:migrate

quel:index-hide

Marks an index as invisible (INVISIBLE on MySQL, IGNORED on MariaDB) so the optimizer will not consider it when planning queries. This is a safe way to evaluate the impact of removing an index before committing to a permanent drop — the index continues to be maintained, so it can be restored instantly with no rebuild cost. If no arguments are supplied the command falls back to interactive prompts.

vendor/bin/sculpt quel:index-hide <entity> <index>

# Examples
vendor/bin/sculpt quel:index-hide User idx_email
vendor/bin/sculpt quel:index-hide OrderLine idx_created_at

quel:index-show

Reverses a previous hide, restoring the index to full optimizer visibility (VISIBLE on MySQL, NOT IGNORED on MariaDB). Because the index was never dropped, making it visible again is instantaneous — there is no rebuild and no downtime.

vendor/bin/sculpt quel:index-show <entity> <index>

# Examples
vendor/bin/sculpt quel:index-show User idx_email
vendor/bin/sculpt quel:index-show OrderLine idx_created_at