How to clean up your PrestaShop database and keep your shop fast

A PrestaShop webshop quietly collects a lot of data. Every visit, every search and every page that does not exist is stored somewhere. After a few years your database can be gigabytes in size, while your products and orders only take up a fraction of that.

That is not just wasted space. Large tables make backups slow, updates more nerve-racking and some back office pages noticeably slower.

Which tables grow the fastest?

In most shops the biggest culprits are in the same place:

1. ps_connections, with ps_connections_page and ps_connections_source: one row for every visit, bots included.
2. ps_guest: anonymous visitors who never created an account.
3. ps_statssearch: every search term visitors have ever typed.
4. ps_pagenotfound: a log of every 404 page.
5. ps_mail: a record of every email your shop has sent.

After a few months you no longer need any of this to run your shop. Your orders, customers and products live in completely different tables.

What can you safely remove?

A good rule of thumb: keep statistics and logs for as long as you actually use them, for example 90 days, and remove the rest. Watch out for two things:

1. Only remove anonymous guests, never guests linked to a customer account.
2. Keep guests with a recent cart, otherwise a visitor who just added something to their cart will lose it.

Expired price rules and the faceted search cache can go as well. PrestaShop rebuilds that cache automatically.

How to do it safely

1. Make a backup first

Never clean up without a backup. If something goes wrong, you simply restore the tables.

2. Check what will be removed

Look at how many rows will be removed per table. If you suddenly see an unexpectedly large number, check your settings first.

3. Delete in small batches

A single DELETE on millions of rows can lock your shop for minutes. Delete in batches of a few thousand rows instead.

4. Rebuild the search index

A fresh search index makes sure your shop search matches your current catalogue again.

5. Give the space back to the disk

MySQL and MariaDB do not return the space of deleted rows to the disk by themselves. If you want the database files to actually shrink, run OPTIMIZE TABLE on the largest tables afterwards, for example in phpMyAdmin.

By hand or with a module?

You can do all of this with SQL queries in phpMyAdmin, but one wrong WHERE clause and your data is gone. And you have to repeat it every few months.

That is why we built Cloverfield Database Clean-up. The module creates a backup in one click, shows in advance how many rows each table will lose, cleans up in batches of 2000 rows and rebuilds the search index. You restore backups from the same module, and multistore is supported too.

In short

1. Logs and statistics make your database grow without you noticing.
2. Only keep what you actually use, for example the last 90 days.
3. Always back up first, then clean up in small batches.
4. Repeat it a few times a year and your shop stays fast.