Magento 2 Database Index Strategy & Query Optimization

Magento 2's database is its backbone. Every product load, category browse, cart update, and checkout step hits MySQL. When your catalog grows past 50K SKUs and traffic climbs, poorly optimized queries become the silent killer — causing slow page loads, checkout timeouts, and frustrated customers.

Most Magento developers know about Redis caching and Varnish, but few dig into the database layer where real performance gains hide. This guide covers practical MySQL index strategies and query optimization techniques tailored specifically for Magento 2's architecture.

Understanding Magento 2's Database Schema

Magento 2 uses a hybrid EAV (Entity-Attribute-Value) and flat table model. Products, customers, and categories store their core data in EAV tables, while indexed data lives in flat tables for faster retrieval.

The EAV Table Structure

A single product's name doesn't live in one row. Instead, it's spread across multiple tables:

catalog_product_entity              → entity_id, sku, type_id
catalog_product_entity_varchar      → entity_id, attribute_id, store_id, value
catalog_product_entity_int          → entity_id, attribute_id, store_id, value
catalog_product_entity_decimal      → entity_id, attribute_id, store_id, value
catalog_product_entity_text         → entity_id, attribute_id, store_id, value
catalog_product_entity_datetime     → entity_id, attribute_id, store_id, value

Loading a single product with 20 attributes means joining 5-6 EAV tables. Each join multiplies the query cost. At 100K products, this becomes brutal without proper indexing.

Flat Index Tables

Magento's indexers create flat tables like catalog_product_flat_1 that denormalize EAV data into a single row per product. These tables are what actually power category pages and search results.

-- Flat table structure (simplified)
SELECT entity_id, sku, name, price, status, visibility, url_key
FROM catalog_product_flat_1
WHERE status = 1 AND visibility IN (2, 4)
ORDER BY position ASC
LIMIT 20;

This query is fast because it hits a single flat table with standard column indexes — no EAV joins needed.

Critical Indexes Every Magento Store Needs

1. Sales Order Indexes

The sales_order and sales_order_grid tables are queried heavily in the admin panel and for order processing:

-- Check existing indexes
SHOW INDEX FROM sales_order_grid;

-- Add composite index for admin order grid filtering
ALTER TABLE sales_order_grid
ADD INDEX idx_status_created (status, created_at);

-- Add index for customer order history
ALTER TABLE sales_order
ADD INDEX idx_customer_id_created (customer_id, created_at DESC);

Without these, loading the admin order grid with 500K+ orders takes 10-30 seconds. With them, it drops to under 1 second.

2. Catalog Product Indexes

The flat product table needs indexes matching your common query patterns:

-- For category pages (most common query)
ALTER TABLE catalog_product_flat_1
ADD INDEX idx_status_visibility_position (status, visibility, position);

-- For product search/filter by attribute set
ALTER TABLE catalog_product_flat_1
ADD INDEX idx_attribute_set_id (attribute_set_id);

-- For price-based sorting and filtering
ALTER TABLE catalog_product_flat_1
ADD INDEX idx_price (price);

3. Cart and Quote Indexes

The quote and quote_item tables slow down as abandoned carts accumulate:

-- Speed up quote lookups by store
ALTER TABLE quote
ADD INDEX idx_store_id_is_active (store_id, is_active);

-- Speed up guest cart retrieval
ALTER TABLE quote
ADD INDEX idx_customer_id_is_active (customer_id, is_active);

-- Quote item lookups
ALTER TABLE quote_item
ADD INDEX idx_quote_id_product_id (quote_id, product_id);

4. URL Rewrite Indexes

URL rewrites are hit on every page load. With thousands of products, the url_rewrite table grows fast:

-- The critical lookup index
ALTER TABLE url_rewrite
ADD INDEX idx_request_path_store_id (request_path, store_id);

-- For redirect lookups
ALTER TABLE url_rewrite
ADD INDEX idx_target_path_store_id (target_path, store_id);

Using EXPLAIN to Identify Slow Queries

Before adding indexes blindly, always analyze with EXPLAIN. Here's how to read the output for Magento-specific queries:

EXPLAIN SELECT e.entity_id, e.sku, e.type_id,
       v1.value AS name, d1.value AS price
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v1
    ON e.entity_id = v1.entity_id
    AND v1.attribute_id = (SELECT attribute_id FROM eav_attribute
                           WHERE attribute_code = 'name'
                           AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_decimal d1
    ON e.entity_id = d1.entity_id
    AND d1.attribute_id = (SELECT attribute_id FROM eav_attribute
                           WHERE attribute_code = 'price'
                           AND entity_type_id = 4)
WHERE e.entity_id = 12345;

What to Look For in EXPLAIN Output

type: ALL — Full table scan. This is the worst case. Add an index.

-- Bad: full table scan on 500K rows
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | e     | ALL  | NULL          | NULL | NULL    | NULL | 487231 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

type: ref — Index lookup using a reference. Good.

type: const — Single row lookup by primary key. Best possible.

Extra: Using filesort — MySQL is sorting results manually. Often means the ORDER BY doesn't match an index.

Extra: Using temporary — Creating a temporary table. Often unavoidable with complex joins but try to minimize.

Magento-Specific Query Patterns to Optimize

Category Product Listing

The most queried page on most stores. Here's the typical pattern:

-- Default Magento category query (simplified)
SELECT entity_id, sku, name, price, thumbnail, url_key
FROM catalog_product_flat_1
WHERE (status = 1)
  AND (visibility IN (2, 4))
  AND (entity_id IN (
      SELECT product_id FROM catalog_category_product
      WHERE category_id = 42
  ))
ORDER BY position ASC
LIMIT 20 OFFSET 0;

The subquery in IN (...) is the performance trap. For large categories, optimize with a JOIN:

-- Optimized with JOIN instead of IN subquery
SELECT f.entity_id, f.sku, f.name, f.price, f.thumbnail, f.url_key
FROM catalog_product_flat_1 f
INNER JOIN catalog_category_product ccp
    ON f.entity_id = ccp.product_id
WHERE f.status = 1
  AND f.visibility IN (2, 4)
  AND ccp.category_id = 42
ORDER BY ccp.position ASC
LIMIT 20 OFFSET 0;

Add the necessary index on the category-product relation table:

ALTER TABLE catalog_category_product
ADD INDEX idx_category_id_position (category_id, position);

Customer Group Price Queries

Tier prices and group prices are loaded per product. With many customer groups, this explodes:

-- Add index for tier price lookups
ALTER TABLE catalog_product_entity_tier_price
ADD INDEX idx_product_id_customer_group (product_id, customer_group_id, qty);

-- Add index for catalog rule price
ALTER TABLE catalogrule_product_price
ADD INDEX idx_product_id_rule_date (product_id, rule_date, customer_group_id);

Inventory / Stock Queries

MSI (Multi-Source Inventory) adds complexity. The inventory_source_item table needs proper indexing:

-- For stock status checks
ALTER TABLE inventory_source_item
ADD INDEX idx_sku_source (sku, source_code);

-- For stock aggregation
ALTER TABLE inventory_stock_1
ADD INDEX idx_product_id_stock (product_id, is_salable);

Monitoring Slow Queries in Production

Enable the MySQL Slow Query Log

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

After enabling, watch for Magento patterns:

# Top 10 slowest queries from the log
pt-query-digest /var/log/mysql/slow.log --limit 10

# Or manually
grep "Query_time" /var/log/mysql/slow.log | sort -t: -k2 -rn | head -20

Using Magento's Built-in Profiler

Enable database profiling in app/etc/env.php:

'db' => [
    'connection' => [
        'default' => [
            'host' => 'localhost',
            'dbname' => 'magento2',
            'username' => 'root',
            'password' => '',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8mb4;',
            'profiler' => [
                'class' => '\Magento\Framework\DB\Profiler',
                'enabled' => true,
            ],
        ],
    ],
],

Then log the profiler output in a custom module or check the var/debug/db.log file for query times.

Connection Pooling and Persistent Connections

Magento opens a new MySQL connection per request by default. Under high traffic, connection establishment becomes a bottleneck:

// app/etc/env.php — enable persistent connections
'db' => [
    'connection' => [
        'default' => [
            'driver_options' => [
                PDO::ATTR_PERSISTENT => true,
            ],
        ],
    ],
],

For even better results, use ProxySQL as a connection pool between PHP and MySQL:

# Install ProxySQL
apt-get install proxysql

# /etc/proxysql.cnf
mysql_users = (
    {
        username = "magento"
        password = "secret"
        default_hostgroup = 0
        max_connections = 200
    }
)

ProxySQL maintains a pool of persistent connections and routes queries efficiently — especially useful when running multiple PHP-FPM workers.

InnoDB Buffer Pool Optimization

The InnoDB buffer pool is MySQL's most important memory setting. It caches data and indexes in RAM:

# my.cnf — set to 70-80% of available RAM for dedicated DB server
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8  # One per GB, up to 64
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2  # Slight risk, big speed gain
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000  # For SSD
innodb_io_capacity_max = 4000

Check if your buffer pool is large enough:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';

-- Key metrics:
-- Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads = hit ratio
-- Should be > 99%

If the hit ratio drops below 99%, increase innodb_buffer_pool_size.

Table Maintenance and Fragmentation

Magento tables fragment over time as products are added, updated, and deleted. Regular maintenance keeps them lean:

-- Check fragmentation
SELECT table_name,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(data_free/1024/1024, 2) AS fragmented_mb,
       ROUND((data_free / data_length) * 100, 2) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'magento2'
  AND data_free > 0
ORDER BY data_free DESC
LIMIT 20;

-- Defragment a table
ALTER TABLE catalog_product_entity ENGINE=InnoDB;
-- or
OPTIMIZE TABLE catalog_product_entity;

Run this monthly during off-peak hours. For zero-downtime optimization, use pt-online-schema-change:

pt-online-schema-change --alter "ENGINE=InnoDB" \
    D=magento2,t=catalog_product_entity \
    --execute

Real-World Results

After implementing these index and query optimizations on a store with 120K SKUs and 2M orders:

Metric	Before	After
Category page load	3.2s	0.8s
Admin order grid	12s	0.9s
Checkout (place order)	8s	1.4s
Search results	2.1s	0.6s
Avg MySQL queries/page	340	85

The biggest wins came from the flat table indexes, replacing IN subqueries with JOINs, and the InnoDB buffer pool increase.

Conclusion

Database optimization is rarely glamorous, but it's where the most impactful performance gains hide. Start with EXPLAIN on your slowest pages, add targeted indexes, tune your InnoDB settings, and maintain your tables regularly. Your Redis and Varnish caches work better when the database underneath them is fast — because cache misses are inevitable, and when they happen, you want the database to respond in milliseconds, not seconds.

Monitor your slow query log weekly, revisit your indexes after major catalog changes, and always test schema changes in staging before production. A well-tuned MySQL layer is the foundation that every other Magento optimization sits on top of.