PrestaShop & E-commerce (2/2): Ottimizzazione Indici MySQL e Query Profiling

In un e-commerce PrestaShop con oltre 100.000 combinazioni, le tabelle di catalogo come ps_product_shop, ps_category_product e ps_stock_available diventano presto il collo di bottiglia principale. Questo articolo affronta il profiling con EXPLAIN ANALYZE, la rimozione dei Filesort e la definizione di indici compositi specifici.

1. L’Anatomia di una Query Lenta su PrestaShop

Durante la navigazione nelle pagine categoria con filtri a faccette attivi, PrestaShop esegue JOIN multiple tra prodotti, categorie, prezzi e stock. In assenza di indici coprenti, il query engine ricorre a scansioni complete (ALL) e tabelle temporanee su disco:

-- Query di campionamento categoria
EXPLAIN ANALYZE
SELECT p.id_product, p.reference, sa.quantity
FROM ps_product p
INNER JOIN ps_product_shop ps ON (p.id_product = ps.id_product AND ps.id_shop = 1)
INNER JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_stock_available sa ON (p.id_product = sa.id_product AND sa.id_shop = 1)
WHERE cp.id_category = 42 AND ps.active = 1
ORDER BY p.date_add DESC
LIMIT 24;

2. Eliminare “Using temporary; Using filesort”

Se l’output di EXPLAIN evidenzia Using temporary; Using filesort, il database sta allocando memoria RAM volatile per riordinare i dati prima di applicare il LIMIT. Per neutralizzare il collo di bottiglia è necessario allineare gli indici compositi con l’ordinamento richiesto:

-- Creazione indice composito coprente per categoria e ordinamento
ALTER TABLE ps_category_product 
ADD INDEX idx_cat_prod_pos (id_category, position, id_product);

-- Indice composito di filtraggio shop
ALTER TABLE ps_product_shop 
ADD INDEX idx_shop_active_date (id_shop, active, date_add, id_product);

3. InnoDB Buffer Pool & Deframmentazione

L’aggiunta di indici aumenta l’impronta su disco delle tabelle. Per evitare letture continue dal sottosistema NVMe:

• Dimensione del Buffer Pool: Impostare innodb_buffer_pool_size ad almeno il 60-75% della RAM totale del server dedicato.
• Ricostruzione indici: Eseguire periodicamente OPTIMIZE TABLE per deframmentare gli indici b-tree ed eliminare lo spazio non allocato nelle pagine InnoDB.
• Monitoraggio hit-ratio: Verificare che Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) sia stabilmente superiore al 99%.