Nettoyer la base de données PrestaShop : le guide SQL

Pourquoi une base gonflée coûte cher

La base de données PrestaShop d'une boutique active grossit continuellement, et l'essentiel de ce volume n'a aucune valeur métier. Les conséquences se voient vite : back-office qui rame, sauvegardes qui dépassent le quota de l'hébergement, restauration qui prend des heures le jour où il faut aller vite, et export impossible via l'interface d'administration de la base.

Le nettoyage est l'une des interventions au meilleur rapport gain/effort. Encore faut-il savoir ce qu'on supprime.

Identifier ce qui pèse

Avant toute chose, mesurez. Cette requête classe vos tables par taille :

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;

Dans la grande majorité des cas, le classement est dominé par les mêmes coupables :

• ps_connections, ps_connections_source, ps_connections_page, ps_guest : le suivi de navigation des visiteurs ;
• ps_page_viewed, ps_statssearch : les statistiques natives ;
• ps_search_index et ps_search_word : l'index de recherche interne ;
• ps_cart et ps_cart_product : les paniers, dont l'immense majorité n'a jamais donné lieu à une commande ;
• ps_log, ps_mail, ps_customer_thread : journaux, historiques d'emails et messages, souvent pollués de spam.

Avant de supprimer quoi que ce soit

Faites une sauvegarde complète et vérifiez qu'elle se restaure. Une purge est irréversible.

Travaillez d'abord en SELECT COUNT(*). Pour chaque suppression envisagée, comptez d'abord les lignes concernées. Un DELETE lancé avec une condition mal écrite ne se rattrape pas.

Passez la boutique en maintenance pour les grosses purges, afin d'éviter les verrous sur des tables en cours d'écriture.

Supprimez par lots. Un DELETE portant sur des millions de lignes peut saturer le journal de transactions. Ajoutez une clause LIMIT 50000 et répétez.

Les purges sûres

Les statistiques de navigation. Elles n'ont de valeur que si vous utilisez réellement les statistiques natives de PrestaShop, ce qui est rare quand un outil d'analyse externe est en place.

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;

Respectez cet ordre : les tables filles d'abord, la table parente ensuite.

Les paniers sans commande. Comptez avant de supprimer :

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);

Puis supprimez les lignes de panier associées avant les paniers eux-mêmes. Attention : si vous exploitez les relances de panier abandonné, gardez une fenêtre plus large, six mois minimum.

Les journaux applicatifs.

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

L'historique des emails. La table ps_mail ne sert qu'à consulter ce qui a été envoyé. Un an de rétention est largement suffisant.

Ce qu'il ne faut jamais supprimer

Les commandes, factures et avoirs. Vous avez une obligation légale de conservation. Une boutique dont on a purgé ps_orders pour gagner de la place n'est pas récupérable.

Les clients rattachés à une commande. Même « inactifs ». La suppression casse l'historique et les factures.

Les tables de statistiques, si un module en dépend. Certains modules d'attribution ou de tableau de bord lisent ps_connections. Vérifiez avant, sinon vous videz leurs rapports.

Les tables de configuration, quelle que soit leur taille apparente.

Réindexer et réorganiser

Après une purge, l'espace n'est pas restitué tant que les tables ne sont pas réorganisées :

OPTIMIZE TABLE ps_connections, ps_connections_page, ps_cart, ps_log;

Régénérez ensuite l'index de recherche depuis Paramètres de la boutique > Recherche > Réindexer la base entière. Sur un catalogue important, l'interface dépasse le temps d'exécution autorisé : passez alors par l'URL de tâche planifiée dédiée, ou traitez par lots.

Vérifiez enfin qu'aucune table n'est restée en MyISAM après d'anciennes migrations :

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

InnoDB gère les verrous au niveau de la ligne et non de la table : la conversion supprime des blocages invisibles mais bien réels sur une boutique active.

En faire une routine

Un nettoyage annuel ne sert pas à grand-chose : la base est de nouveau saturée six mois plus tard. Mettez en place une purge automatique mensuelle sur les tables de statistiques et de journaux, avec une rétention décidée une fois pour toutes.

Si votre back-office reste lent après le nettoyage, le problème est ailleurs : voyez notre article sur l'accélération d'une boutique PrestaShop.

Faire auditer votre base

Nous mesurons, purgeons et mettons en place la routine de nettoyage, sans jamais toucher aux données comptables. Voir notre offre de maintenance PrestaShop.