The PrestaShop database: growing tables, indexes and safe clean-up

The PrestaShop database grows with every visit, not just with every order. After a few years, it is common to find tables with millions of rows of statistics that nobody looks at and that slow down backups and some screens.

The tables that grow the most

Safe clean-up

1. A full backup before anything else.
2. Delete by date and in batches, not all at once, so you do not lock the store:

DELETE FROM ps_connections
 WHERE date_add < NOW() - INTERVAL 12 MONTH
 LIMIT 50000;

3. For carts, delete only those without an order:

DELETE c 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_upd < NOW() - INTERVAL 6 MONTH;

Slow queries

Turn on the slow query log of the MySQL or MariaDB server (slow_query_log) for a day of normal traffic. Queries that appear repeatedly usually come from a specific module and are almost always fixed with an index.

Server settings

If you manage the server, the parameter with the most influence is innodb_buffer_pool_size: ideally, large enough for the tables used every day to fit in memory. But on shared servers or those with little RAM, raising it without measuring can leave the system out of memory: size it to the real size of your data.

Less data, fewer problems

Disable the statistics you do not use (stats* modules) and schedule a clean-up once a month. Your database, your backups and your migrations will all be faster.