Pulire il database di PrestaShop: la guida SQL

Perché un database gonfio costa caro

Il database di un negozio PrestaShop attivo cresce di continuo, e la maggior parte di quel volume non ha alcun valore per il business. Le conseguenze si vedono in fretta: back office lento, backup che sforano la quota dell'hosting, ripristino che richiede ore proprio quando bisogna fare in fretta, ed export impossibile dall'interfaccia di amministrazione del database.

La pulizia è uno degli interventi con il miglior rapporto fra risultato e sforzo. A patto di sapere che cosa si elimina.

Individuare che cosa pesa

Prima di tutto, misuri. Questa query ordina le tabelle per dimensione:

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS taille_mo,
       table_rows
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 25;

Nella grande maggioranza dei casi la classifica è dominata dai soliti colpevoli:

• ps_connections, ps_connections_source, ps_connections_page, ps_guest: il tracciamento della navigazione dei visitatori;
• ps_page_viewed, ps_statssearch: le statistiche native;
• ps_search_index e ps_search_word: l'indice della ricerca interna;
• ps_cart e ps_cart_product: i carrelli, la stragrande maggioranza dei quali non si è mai trasformata in un ordine;
• ps_log, ps_mail, ps_customer_thread: log, storico delle email e messaggi, spesso pieni di spam.

Prima di cancellare qualsiasi cosa

Faccia un backup completo e verifichi che si ripristini. Una pulizia è irreversibile.

Lavori prima in SELECT COUNT(*). Per ogni cancellazione prevista, conti prima le righe coinvolte. Un DELETE lanciato con una condizione scritta male non si recupera.

Metta il negozio in manutenzione per le pulizie importanti, così da evitare i lock sulle tabelle in scrittura.

Cancelli a lotti. Un DELETE su milioni di righe può saturare il log delle transazioni. Aggiunga una clausola LIMIT 50000 e ripeta.

Le pulizie sicure

Le statistiche di navigazione. Hanno valore soltanto se usa davvero le statistiche native di PrestaShop, cosa rara quando è già attivo uno strumento di analisi esterno.

DELETE FROM ps_connections_page WHERE id_connections IN (
  SELECT id_connections FROM ps_connections WHERE date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH)
) LIMIT 50000;

DELETE FROM ps_connections_source WHERE date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH) LIMIT 50000;
DELETE FROM ps_connections WHERE date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH) LIMIT 50000;

Rispetti questo ordine: prima le tabelle figlie, poi quella padre.

I carrelli senza ordine. Conti prima di cancellare:

SELECT COUNT(*) FROM ps_cart c
LEFT JOIN ps_orders o ON o.id_cart = c.id_cart
WHERE o.id_order IS NULL AND c.date_add < DATE_SUB(NOW(), INTERVAL 6 MONTH);

Poi elimini le righe di carrello associate prima dei carrelli stessi. Attenzione: se sfrutta i solleciti dei carrelli abbandonati, tenga una finestra più ampia, almeno sei mesi.

I log applicativi.

DELETE FROM ps_log WHERE date_add < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 50000;

Lo storico delle email. La tabella ps_mail serve soltanto a consultare ciò che è stato inviato. Un anno di conservazione è più che sufficiente.

Che cosa non va mai cancellato

Ordini, fatture e note di credito. Esiste un obbligo di legge di conservazione. Un negozio a cui è stata svuotata ps_orders per guadagnare spazio non è recuperabile.

I clienti collegati a un ordine. Anche quelli «inattivi». La cancellazione rompe lo storico e le fatture.

Le tabelle delle statistiche, se un modulo ne dipende. Alcuni moduli di attribuzione o di dashboard leggono ps_connections. Verifichi prima, altrimenti ne svuota i report.

Le tabelle di configurazione, quale che sia la loro dimensione apparente.

Reindicizzare e riorganizzare

Dopo una pulizia lo spazio non viene restituito finché le tabelle non vengono riorganizzate:

OPTIMIZE TABLE ps_connections, ps_connections_page, ps_cart, ps_log;

Poi rigeneri l'indice della ricerca da Parametri del negozio > Ricerca, con la reindicizzazione completa del database. Su un catalogo importante l'interfaccia supera il tempo di esecuzione consentito: in quel caso passi dall'URL dell'attività pianificata dedicata, oppure proceda per lotti.

Verifichi infine che nessuna tabella sia rimasta in MyISAM dopo vecchie migrazioni:

SELECT table_name, engine FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND engine <> 'InnoDB';

InnoDB gestisce i lock a livello di riga e non di tabella: la conversione elimina blocchi invisibili ma molto concreti su un negozio attivo.

Trasformarla in una routine

Una pulizia all'anno serve a poco: sei mesi dopo il database è di nuovo saturo. Metta in piedi una pulizia automatica mensile sulle tabelle di statistiche e di log, con una conservazione decisa una volta per tutte.

Se il back office resta lento dopo la pulizia, il problema è altrove: veda il nostro articolo su come accelerare un negozio PrestaShop.

Far analizzare il suo database

Misuriamo, ripuliamo e mettiamo in piedi la routine di pulizia, senza mai toccare i dati contabili. Scopra la nostra offerta di manutenzione PrestaShop.