Magento 2 database optimalisatie — queries die je shop vertragen

-- Controleer huidige status
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- Inschakelen voor de huidige sessie
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_time = 1;  -- Log queries die langer dan 1 seconde duren
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Queries zonder index ook loggen
SET GLOBAL log_queries_not_using_indexes = 'ON';

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Installeer Percona Toolkit
sudo apt-get install percona-toolkit -y

# Analyseer slow query log
pt-query-digest /var/log/mysql/slow.log | head -100

# Rank Query ID           Response time Calls  R/Call  V/M   Item
# ==== ================== ============= ====== ======= ===== ====
#    1 0x12E3A9F4         124.2321 40%   1823  0.0681  0.00  SELECT catalog_product_index_price
#    2 0x8B7F2C1A          89.1432 29%   2104  0.0424  0.00  SELECT catalog_category_product_index
#    3 0x4A2E8D7C          54.3211 18%    987  0.0550  0.00  SELECT search_tmp_

-- Typische Magento EAV-query (vereenvoudigd)
SELECT e.entity_id, e.sku,
       at_name.value AS name,
       at_price.value AS price,
       at_status.value AS status
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar at_name
    ON at_name.entity_id = e.entity_id AND at_name.attribute_id = 73
LEFT JOIN catalog_product_entity_decimal at_price
    ON at_price.entity_id = e.entity_id AND at_price.attribute_id = 77
LEFT JOIN catalog_product_entity_int at_status
    ON at_status.entity_id = e.entity_id AND at_status.attribute_id = 97
WHERE e.entity_id IN (1, 2, 3, 4, 5, ...)  -- soms 1000+ IDs

php bin/magento config:set catalog/frontend/flat_catalog_category 1
php bin/magento config:set catalog/frontend/flat_catalog_product 1
php bin/magento indexer:reindex catalog_category_flat catalog_product_flat

-- Identificeer queries zonder index
EXPLAIN SELECT cp.entity_id
FROM catalog_product_index_eav cpie
WHERE cpie.attribute_id = 93    -- kleur
  AND cpie.value = 'Rood'
  AND cpie.store_id = 1;

-- Als type = 'ALL' in de EXPLAIN-output: geen index gebruikt
-- Als type = 'ref' of 'range': index gebruikt

-- Index voor layered navigation queries
ALTER TABLE catalog_product_index_eav
    ADD INDEX idx_attr_val_store (attribute_id, value, store_id);

-- Index voor prijsfiltering
ALTER TABLE catalog_product_index_price
    ADD INDEX idx_website_group_store (website_id, customer_group_id, store_id);

// Problematisch: aparte query per product
foreach ($products as $product) {
    $price = $this->priceHelper->getPrice($product); // 1 query per product
    $reviews = $this->reviewFactory->getEntitySummary($product); // nog 1 query
}

// Beter: batch-loading via de collection
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect(['name', 'price', 'small_image', 'url_key']);
$collection->addMinimalPrice();
$collection->addFinalPrice();
$collection->addTaxPercents();
$collection->load();

-- Controleer de omvang
SELECT COUNT(*) as totaal_quotes,
       COUNT(CASE WHEN is_active = 0 THEN 1 END) as verlaten_quotes,
       COUNT(CASE WHEN updated_at < DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as oud_30_dagen
FROM quote;

php bin/magento config:set catalog/magento_catalogpermissions/grant_catalog_category_view 0

# Of via de Magento CLI
php bin/magento cron:run --group=index

-- Verwijder verlaten quotes ouder dan 90 dagen
DELETE FROM quote
WHERE is_active = 0
  AND updated_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Bijbehorende quote items worden via foreign key constraint verwijderd
-- (als cascade delete is geconfigureerd)

-- Controleer tabelgroottes
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'jouw_database_naam'
ORDER BY data_length DESC
LIMIT 20;

EXPLAIN SELECT e.entity_id, e.sku
FROM catalog_product_entity e
JOIN catalog_product_index_price pip
    ON pip.entity_id = e.entity_id
    AND pip.website_id = 1
    AND pip.customer_group_id = 0
WHERE pip.final_price BETWEEN 50 AND 100
ORDER BY pip.final_price ASC
LIMIT 24;

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# InnoDB buffer pool: stel in op 70-80% van het beschikbare RAM
# Op een server met 8GB RAM:
innodb_buffer_pool_size = 6G

# Buffer pool instances: 1 per GB buffer pool, max 8
innodb_buffer_pool_instances = 6

# Query cache uitschakelen (schaalt slecht bij veel writes)
query_cache_type = 0
query_cache_size = 0

# Verhoog open files limit voor grote Magento-installaties
open_files_limit = 65536
table_open_cache = 4096

# Logboek voor slow queries
slow_query_log = 1
long_query_time = 1