MySQL Query Optimization

Using EXPLAIN

-- Basic EXPLAIN
EXPLAIN SELECT * FROM catalog_product_entity WHERE sku = 'TSHIRT-001';
+------+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table                      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | catalog_product_entity     | ref  | IDX_SKU       | IDX_SKU | 768   | const|    1 | Using index |
+------+-------------+----------------------------+------+---------------+------+---------+------+------+-------------+

EXPLAIN Output Fields

Access Type Ratings

Best: system > const > eq_ref > ref > range > index > All
Worst: ALL (full table scan - avoid!)

const: Single row lookup (primary key)
eq_ref: One row from index join
ref: Multiple rows from index
range: Index range scan
index: Full index scan
ALL: Full table scan (bad!)

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON 
SELECT p.entity_id, v.value AS name, d.value AS price
FROM catalog_product_entity p
JOIN catalog_product_entity_varchar v ON v.entity_id = p.entity_id AND v.attribute_id = 71
JOIN catalog_product_entity_decimal d ON d.entity_id = p.entity_id AND d.attribute_id = 75
WHERE p.sku = 'TSHIRT-001';

-- Output includes query cost, actual rows, and detailed execution plan

Enable Slow Query Log

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2              # Log queries taking > 2 seconds
log_queries_not_using_indexes = 1 # Log queries without indexes
min_examined_row_limit = 1000     # Only log queries examining 1000+ rows

Analyzing Slow Queries

# Find slow queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Output:
# Count: 150  Time=2.50s (375s)  Lock=0.00s (0s)  Rows=10000.0 (1500000)
# SELECT * FROM catalog_product_entity WHERE type_id = 'N'

Using pt-query-digest

# Install Percona Toolkit
apt-get install percona-toolkit

# Analyze slow log
pt-query-digest /var/log/mysql/slow-query.log > slow-report.txt

# Output includes:
# - Query fingerprint
n# - Execution count
# - Average time
# - Lock time
# - Rows examined

Magento Debug Mode

// Enable query logging in development
// app/config.php
return [
    'db' => [
        'connections' => [
            'default' => [
                'log_profiler' => 1,
                'profiler' => 1
            ]
        ]
    ]
];

// Or via environment variable
// MAGENTO_DB_LOG_PROFILER=1

Query Performance Monitoring

-- Check current queries
SHOW PROCESSLIST;

-- Kill long-running queries
KILL <process_id>;

-- Check InnoDB status
SHOW ENGINE INNODB STATUS;

-- Monitor query cache
SHOW STATUS LIKE 'Qcache%';

Index Design Principles

-- 1. Index columns used in WHERE
CREATE INDEX idx_sku ON catalog_product_entity(sku);

-- 2. Index columns used in JOINs
CREATE INDEX idx_product_id ON catalog_product_entity_varchar(entity_id, attribute_id);

-- 3. Index columns used in ORDER BY
CREATE INDEX idx_price ON catalog_product_entity_decimal(entity_id, attribute_id);

-- 4. Composite indexes for multiple conditions
CREATE INDEX idx_status_visibility ON catalog_product_entity_int(entity_id, attribute_id)
WHERE attribute_id IN (97, 99); -- status, visibility

Magento Index Recommendations

-- Product listing query optimization
-- Before: slow query
SELECT p.entity_id, v.value AS name, d.value AS price
FROM catalog_product_entity p
LEFT JOIN catalog_product_entity_varchar v ON v.entity_id = p.entity_id AND v.attribute_id = 71
LEFT JOIN catalog_product_entity_decimal d ON d.entity_id = p.entity_id AND d.attribute_id = 75
WHERE v.value LIKE '%T-Shirt%'
ORDER BY d.value ASC
LIMIT 20;

-- After: optimized with indexes
CREATE INDEX idx_varchar_entity_attr ON catalog_product_entity_varchar(entity_id, attribute_id, value(100));
CREATE INDEX idx_decimal_entity_attr ON catalog_product_entity_decimal(entity_id, attribute_id, value);

Index Monitoring

-- Find unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- Find redundant indexes
SELECT * FROM sys.schema_redundant_indexes;

-- Check index usage
SHOW STATUS LIKE 'Handler_read%';

-- Index cardinality
SELECT index_name, column_name, cardinality
FROM information_schema.statistics
WHERE table_name = 'catalog_product_entity'
ORDER BY index_name, seq_in_index;

Avoid N+1 Queries

// BAD: N+1 queries
$products = $collection->load(); // 1 query
foreach ($products as $product) {
    $name = $product->getName();    // N queries if not loaded
    $price = $product->getPrice();  // N queries if not loaded
}

// GOOD: Load specific attributes
$collection->addAttributeToSelect(['name', 'price', 'sku']);
// Single query with JOINs

Optimize Collection Loading

// Use field filters instead of addFieldToFilter for EAV
$collection->addFieldToFilter('status', 1);
// This uses index

// vs addAttributeToFilter for EAV attributes
$collection->addAttributeToFilter('status', 1);
// This uses EAV JOINs (slower)

// Use flat table for listing
$flatCollection = $objectManager->get(
    \Magento\Catalog\Model\ResourceModel\Product\Flat\Collection::class
);

Batch Processing

// Process large datasets in batches
$batchSize = 1000;
$collection = $productCollection->create();
$collection->addFieldToFilter('status', 1);

$page = 1;
while (true) {
    $collection->setPageSize($batchSize)->setCurPage($page);
    $collection->load();

    if ($collection->count() === 0) {
        break;
    }

    foreach ($collection as $product) {
        // Process product
    }

    $page++;
    $collection->clear();
}

Query Optimization Checklist

1. Use EXPLAIN on slow queries
2. Add indexes for WHERE, JOIN, ORDER BY columns
3. Avoid SELECT * - fetch only needed columns
4. Use LIMIT for large result sets
5. Avoid LIKE '%value%' (leading wildcard)
6. Use UNION ALL instead of UNION (no dedup)
7. Optimize subqueries to JOINs
8. Use covering indexes
9. Monitor slow query log
10. Profile with pt-query-digest