PrestaShop-database opschonen: de SQL-gids

Waarom een opgeblazen database geld kost

De PrestaShop-database van een actieve webshop groeit onafgebroken, en het grootste deel van dat volume heeft geen enkele bedrijfswaarde. De gevolgen zijn snel zichtbaar: een trage back-office, back-ups die over het quotum van de hosting gaan, een restore die uren duurt op de dag dat het snel moet, en een export die via de beheerinterface van de database niet meer lukt.

Opschonen is een van de ingrepen met de beste verhouding tussen opbrengst en inspanning. Dan moet u wel weten wat u weggooit.

Vaststellen wat er weegt

Meet eerst. Deze query sorteert uw tabellen op omvang:

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;

In de meeste gevallen wordt de ranglijst door dezelfde schuldigen aangevoerd:

• ps_connections, ps_connections_source, ps_connections_page, ps_guest: het volgen van het bezoekersgedrag;
• ps_page_viewed, ps_statssearch: de ingebouwde statistieken;
• ps_search_index en ps_search_word: de interne zoekindex;
• ps_cart en ps_cart_product: de winkelwagens, waarvan de overgrote meerderheid nooit tot een bestelling heeft geleid;
• ps_log, ps_mail, ps_customer_thread: logboeken, e-mailhistorie en berichten, vaak vervuild met spam.

Voordat u iets verwijdert

Maak een volledige back-up en controleer of die terug te zetten is. Een opschoning is onomkeerbaar.

Werk eerst met SELECT COUNT(*). Tel voor elke voorgenomen verwijdering eerst de betrokken regels. Een DELETE met een verkeerd geschreven voorwaarde valt niet terug te draaien.

Zet de webshop in onderhoudsmodus bij grote opschoonacties, om locks te vermijden op tabellen waarnaar wordt geschreven.

Verwijder in batches. Een DELETE over miljoenen regels kan het transactielogboek laten vollopen. Voeg een LIMIT 50000 toe en herhaal.

De veilige opschoonacties

De navigatiestatistieken. Die hebben alleen waarde als u de ingebouwde statistieken van PrestaShop daadwerkelijk gebruikt, wat zelden het geval is zodra er een externe analysetool draait.

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;

Houd deze volgorde aan: eerst de onderliggende tabellen, daarna de bovenliggende.

Winkelwagens zonder bestelling. Tel voordat u verwijdert:

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

Verwijder daarna eerst de bijbehorende winkelwagenregels en pas dan de winkelwagens zelf. Let op: gebruikt u herinneringen voor verlaten winkelwagens, houd dan een ruimer venster aan, minimaal zes maanden.

De applicatielogboeken.

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

De e-mailhistorie. De tabel ps_mail dient alleen om terug te zien wat er verstuurd is. Een jaar bewaren is ruimschoots voldoende.

Wat u nooit mag verwijderen

Bestellingen, facturen en creditnota's. Daarvoor geldt een wettelijke bewaarplicht. Een webshop waarbij ps_orders is leeggemaakt om ruimte te winnen, valt niet meer te redden.

Klanten die aan een bestelling gekoppeld zijn. Ook als ze "inactief" zijn. Verwijderen breekt de historie en de facturen.

Statistiektabellen waarvan een module afhankelijk is. Sommige attributie- of dashboardmodules lezen ps_connections. Controleer dat vooraf, anders maakt u hun rapportages leeg.

Configuratietabellen, ongeacht hun schijnbare omvang.

Herindexeren en reorganiseren

Na een opschoning komt de ruimte pas vrij als de tabellen zijn gereorganiseerd:

OPTIMIZE TABLE ps_connections, ps_connections_page, ps_cart, ps_log;

Bouw daarna de zoekindex opnieuw op via Winkelinstellingen > Zoeken > De hele database opnieuw indexeren. Bij een grote catalogus overschrijdt de interface de toegestane uitvoeringstijd: gebruik dan de speciale URL van de geplande taak, of werk in batches.

Controleer tot slot of er na oude migraties geen tabel op MyISAM is blijven staan:

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

InnoDB werkt met locks op rijniveau in plaats van op tabelniveau: de conversie neemt blokkades weg die onzichtbaar zijn maar op een drukke webshop wel degelijk meetellen.

Maak er een routine van

Eén keer per jaar opschonen levert weinig op: zes maanden later zit de database weer vol. Zet een maandelijkse automatische opschoning op voor de statistiek- en logtabellen, met een bewaartermijn die u één keer vastlegt.

Blijft uw back-office traag na het opschonen, dan zit het probleem elders: zie ons artikel over het versnellen van een PrestaShop-webshop.

Uw database laten doorlichten

Wij meten, ruimen op en zetten de opschoonroutine op, zonder ooit aan uw boekhoudkundige gegevens te komen. Bekijk ons aanbod voor PrestaShop-onderhoud.