1.07

Index Functionality

Deep dive into indexing concepts: performance vs redundancy, price calculations, change logs, MySQL triggers, index modes, and available indexes.

Core Concept: Indexing improves performance of read operations at the cost of data redundancy. Calculate complex values beforehand and store them for fast retrieval.

Indexing Architecture

mindmap root((Indexing)) Core Concept Improve read performance Data redundancy Pre-calculation Problem Complex calculations Many products Sort/filter needs Solution Calculate beforehand Store in MySQL tables Fast retrieval Change Tracking _cl tables MySQL triggers Database level Index Modes Update on Save Immediate CPU intensive Update on Schedule Cron-based Delayed Available Indexes Product Price Category Products Catalog Search Stock Customer Grid

The Problem: Complex Calculations

Example: Product Pricing

Pricing logic in Magento 2 is complex and involves multiple price types and modifiers:

  • Base Price - Regular product price
  • Special Price - Temporary discount
  • Tier Price - Quantity-based pricing
  • Catalog Price Rule - Rule-based discounts
  • Custom Option Price - Additional options cost
The Challenge:

Calculating the final price on-the-fly for a single product is fine, but calculating it for thousands of products simultaneously (e.g., for sort/filter on category page) causes severe performance issues.

Product types add more complexity:

  • Simple Products - Straightforward pricing
  • Configurable Products - Option-dependent pricing
  • Bundle Products - Multiple products with dynamic pricing
  • Grouped Products - Minimum price from associated products

The Solution: Pre-Calculation

Core Indexing Concept

Solution: Calculate the single value final_price beforehand and store it in a MySQL table, so when sorting/filtering is needed, use that pre-calculated value.
Without Indexing:

Category Page Load → Calculate ALL product prices → Sort → Display

Result: ⚠️ SLOW (multi-stage calculations for every product)

With Indexing:

Category Page Load → Read pre-calculated prices → Sort → Display

Result: ✅ FAST (simple MySQL sort on indexed values)

Trade-Off: We sacrifice storage space (data redundancy) and accept that indexed data needs updating when raw data changes, but gain massive performance improvements for read operations.

How Indexing Works

The Indexing Process

  1. Data Change Occurs - Product price updated, catalog rule created, inventory changed
  2. Change Logged - MySQL trigger records change in a *_cl (change log) table
  3. Indexing Triggered - Either immediately (Update on Save) or via cron (Update on Schedule)
  4. Calculation Performed - Series of MySQL queries transform raw data
  5. Index Updated - Final calculated values stored in index tables
  6. Fast Retrieval - Category pages, search, etc. use pre-calculated values
Important: final_price is redundant data - it's calculated from other sources. Whenever the original data changes, final_price must be updated. This is the indexing process.

Change Logs (_cl Tables)

Tracking Changes

Magento tracks changes using special MySQL tables with the _cl suffix (change log).

Change Log Table Tracks Changes For
catalog_product_price_cl Product price changes
catalogsearch_fulltext_cl Full-text search data
catalog_category_product_cl Category-product associations
cataloginventory_stock_cl Stock/inventory changes
How It Works:
  • MySQL Triggers - Automatically record changes at the database level
  • Entity IDs Logged - Only entity IDs are stored, not full data
  • Processed by Cron - Cron job reads change logs and runs indexing
  • Cleared After Processing - Change logs cleared after successful indexing

Index Modes

Two Operating Modes

Update on Save (Realtime)

Behavior: Index updates immediately when data changes

Pros:

  • Always up-to-date
  • No delay
  • No cron needed

Cons:

  • CPU intensive
  • Slows down save operations
  • Can freeze large sites
Update on Schedule

Behavior: Index updates via cron job

Pros:

  • Doesn't slow saves
  • Better for large catalogs
  • Batched processing

Cons:

  • Requires working cron
  • Data may be stale
  • Delay before updates visible
⚠️ Performance Warning:

"Update on Save" can bring local development machines to a standstill with large catalogs. Use "Update on Schedule" but ensure cron is running!

Set mode via CLI:

bin/magento indexer:set-mode realtime
bin/magento indexer:set-mode schedule
bin/magento indexer:set-mode schedule catalog_product_price

Available Indexes

Standard Magento Indexes

View all available indexes:

bin/magento indexer:info
Index Code Name Purpose
catalog_product_price Product Price Pre-calculates final product prices
catalog_category_product Category Products Product-to-category associations
catalog_product_category Product Categories Category-to-product associations
catalogsearch_fulltext Catalog Search Full-text search data
catalogrule_rule Catalog Rule Product Catalog price rules
catalogrule_product Catalog Product Rule Rule-to-product associations
catalog_product_attribute Product EAV EAV attribute data
cataloginventory_stock Stock Inventory/stock status
customer_grid Customer Grid Customer admin grid data
design_config_grid Design Config Grid Design configuration data
Note: Don't confuse Magento indexes with MySQL native indexes! These are separate concepts. Magento indexes are MySQL tables that store calculated data.

Creating Custom Indexes

Magento has a complex indexing engine that developers can use to create custom indexes, but it's rarely needed.

When to Consider Custom Indexes:
  • Complex calculations needed for large datasets
  • Frequent sort/filter operations on custom attributes
  • Performance bottlenecks in custom modules
Caution: Creating custom indexes is complex and beyond the scope of the exam. Most use cases are handled by existing indexes.

Managing Indexes

Common Index Commands

Command Purpose
indexer:info List all available indexers
indexer:status Show status of all indexers
indexer:reindex Reindex all or specific indexers
indexer:set-mode Set realtime or schedule mode
indexer:show-mode Display current mode
indexer:reset Reset indexer status to invalid

Best Practices

Development
  • Use "Update on Schedule" mode
  • Ensure cron is running (* * * * * cd /path/to/magento && php bin/magento cron:run)
  • Manually reindex after bulk changes
  • Monitor change log table sizes
Production
  • Always use "Update on Schedule" mode
  • Set up dedicated cron jobs for indexing
  • Monitor indexer status regularly
  • Schedule full reindex during low-traffic periods
Common Issues
  • Cron not running - Indexes never update in Schedule mode
  • Change logs too large - Can slow down indexing process
  • Locked indexers - Previous indexing job didn't complete
  • Invalid state - Requires manual reindex

Exam Tips

Key Points to Remember

  • Core concept: Indexing improves read performance at the cost of data redundancy
  • Indexes store pre-calculated values (like final_price) for fast retrieval
  • Change logs (*_cl tables) track changes using MySQL triggers
  • Two modes: Update on Save (realtime) and Update on Schedule (cron-based)
  • Update on Save can freeze large sites - use Update on Schedule in production
  • Magento indexes are NOT the same as MySQL native indexes
  • Common indexes: product_price, category_product, catalogsearch_fulltext, stock
  • Indexing happens at database level via MySQL triggers and queries
  • Cron is required for "Update on Schedule" mode to work
  • Use indexer:info to see all available indexes
  • Creating custom indexes is possible but rarely needed
  • Price indexing is the classic example due to complex calculation logic
Previous: 1.06 CLI Commands Next: 1.08 Localization