Search Indexes — Product, Category, and Suggestions

Product Index Structure

-- Fulltext index table
CREATE TABLE catalogsearch_fulltext (
    product_id INT,
    store_id INT,
    data_index TEXT,  -- Combined searchable text
    PRIMARY KEY (product_id, store_id)
);

Indexed Product Data

• Product name
• SKU
• Description
• Short description
• Custom searchable attributes

Reindexing Product Index

# Full reindex
php bin/magento indexer:reindex catalogsearch_fulltext

# Check status
php bin/magento indexer:status catalogsearch_fulltext

# Set to real-time
php bin/magento indexer:set-mode realtime catalogsearch_fulltext

Product Index Data Flow

// Product save triggers index update
public function afterSave()
{
    parent::afterSave();
    // In schedule mode: mark as invalid
    // In real-time: reindex immediately
}

Searchable Attributes

// Check searchable attributes
$eavConfig = $objectManager->get(\Magento\Eav\Model\Config::class);
$attributes = $eavConfig->getEntityAttributes('catalog_product');

foreach ($attributes as $attribute) {
    if ($attribute->getIsSearchable()) {
        echo $attribute->getAttributeCode() . PHP_EOL;
    }
}

Category Index Purpose

Maps products to categories for filtered search:

-- Category-product mapping
CREATE TABLE catalog_category_product (
    category_id INT,
    product_id INT,
    position INT
);

Category Search Features

• Filter search by category
• Category-specific results
• Category navigation in search

Category Reindex

# Reindex category mapping
php bin/magento indexer:reindex catalog_category_product

# Check status
php bin/magento indexer:status catalog_category_product

Category Configuration

// Enable category search
'catalog' => [
    'search' => [
        'enable_search' => 1,
        'search_in_category' => 1
    ]
]

Category Filter in Search

// Search within category
$query = $this->searchQueryFactory->create();
$query->setSearchParam('category_id', $categoryId);
$query->setSearchParam('q', $searchTerm);

Search Suggestions

// Enable suggestions
'catalog/search' => [
    'suggestion' => [
        'enabled' => 1,
        'max_suggestions' => 5
    ]
]

Autocomplete

// Autocomplete configuration
'catalog/search' => [
    'autocomplete' => 1,
    'max_count' => 10,
    'min_query_length' => 2
]

Synonyms

// Synonym configuration
// Admin: Marketing > Search Synonyms

// Example synonyms:
// laptop -> notebook, portable computer
// phone -> mobile, cellphone
// tv -> television, flat screen

Synonym Groups

// Group synonyms together
// laptop, notebook, portable computer
// phone, mobile, cellphone
// tv, television, flat screen

Search Term Management

// Track search terms
// Admin: Marketing > Search Terms

// See what users search for
// Identify missing products
// Add synonyms for popular terms

Index Performance

# Check index size
mysql -u root -p -e "SELECT COUNT(*) FROM catalogsearch_fulltext;"

# Check index health
curl -X GET "http://localhost:9200/_cat/indices?v"

Optimization Techniques

// Batch reindex for large catalogs
$batchSize = 1000;
$productIds = range(1, 100000);

$chunks = array_chunk($productIds, $batchSize);
foreach ($chunks as $chunk) {
    $indexer->reindexList($chunk);
}

Index Monitoring

-- Check reindex status
SELECT indexer_id, status, updated_at
FROM indexer_state
WHERE indexer_id = 'catalogsearch_fulltext';

-- Check index size
SELECT
    table_rows,
    data_length,
    index_length
FROM information_schema.TABLES
WHERE table_name = 'catalogsearch_fulltext';

Best Practices

1. Use real-time mode for small catalogs
2. Use schedule mode for large catalogs
3. Monitor index health regularly
4. Batch reindex for bulk operations
5. Clean stale search terms periodically