Cleaning up the PrestaShop database: the SQL guide

Why a bloated database costs you

The PrestaShop database of an active store grows continuously, and most of that volume has no business value whatsoever. The consequences show up quickly: a sluggish back office, backups that exceed the hosting quota, a restore that takes hours on the day you need to move fast, and exports that fail from the database administration interface.

Cleaning up is one of the best returns on effort available. You just need to know what you are deleting.

Working out what is heavy

Measure first. This query ranks your tables by size:

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 the vast majority of cases, the ranking is dominated by the same culprits:

• ps_connections, ps_connections_source, ps_connections_page, ps_guest: visitor browsing tracking;
• ps_page_viewed, ps_statssearch: the native statistics;
• ps_search_index and ps_search_word: the internal search index;
• ps_cart and ps_cart_product: baskets, the overwhelming majority of which never became an order;
• ps_log, ps_mail, ps_customer_thread: logs, email history and messages, often full of spam.

Before you delete anything

Take a full backup and check that it restores. A purge cannot be undone.

Work in SELECT COUNT(*) first. For every deletion you are considering, count the affected rows first. A DELETE run with a badly written condition cannot be taken back.

Put the store into maintenance mode for large purges, to avoid locks on tables that are being written to.

Delete in batches. A DELETE covering millions of rows can flood the transaction log. Add a LIMIT 50000 clause and repeat.

The safe purges

Browsing statistics. These are only worth keeping if you genuinely use PrestaShop's native statistics, which is rare once an external analytics tool is in 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;

Keep to that order: child tables first, the parent table afterwards.

Baskets with no order. Count before you delete:

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

Then delete the associated basket lines before the baskets themselves. One warning: if you run abandoned basket reminders, keep a wider window, six months at the very least.

Application logs.

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

Email history. The ps_mail table exists only so you can look up what has been sent. A year of retention is more than enough.

What you must never delete

Orders, invoices and credit notes. You have a legal obligation to retain them. A store whose ps_orders table has been purged to save space cannot be recovered.

Customers attached to an order. Even "inactive" ones. Deleting them breaks the order history and the invoices.

Statistics tables, if a module depends on them. Some attribution or dashboard modules read ps_connections. Check first, or you will empty their reports.

Configuration tables, whatever size they appear to be.

Reindex and reorganise

After a purge, the space is not given back until the tables are reorganised:

OPTIMIZE TABLE ps_connections, ps_connections_page, ps_cart, ps_log;

Then rebuild the search index from Shop Parameters > Search > Re-build the entire index. On a large catalogue, the interface goes past the permitted execution time: in that case use the dedicated scheduled task URL, or work in batches.

Finally, check that no table has been left on MyISAM by old migrations:

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

InnoDB locks at row level rather than table level: converting removes blocking that is invisible but very real on a busy store.

Turning it into a routine

An annual clean-up achieves very little: the database is saturated again six months later. Set up an automatic monthly purge on the statistics and log tables, with a retention period decided once and for all.

If your back office is still slow after the clean-up, the problem lies elsewhere: see our article on speeding up a PrestaShop store.

Have your database audited

We measure, purge and set up the cleaning routine, without ever touching accounting data. See our PrestaShop maintenance service.