Cleaning PrestaShop database

The PrestaShop database can grow significantly over time, and not all the data collected there is needed throughout the entire operation of the store. In this article, I’ll share a few SQL queries that will help slim down the database.

Database prefix

In this article, table names are intentionally written as PREFIX_table_name, because the prefix is something you should adapt to your own setup — I trust you’re not using the default ps_, and if you are, I suggest changing it. After copying the SQL query, remember to replace all instances of PREFIX. I also recommend making a backup before running any of the queries below.

Cleaning the PREFIX_guest and PREFIX_cart tables

The PREFIX_guest table stores data about visitors, but over time it also accumulates records that are no longer useful. One way to clean this table is to remove all records that didn’t result in a purchase, customer registration, or cart creation.

The following query will delete abandoned carts older than one month:

DELETE FROM PREFIX_cart
WHERE id_cart NOT IN (SELECT id_cart FROM PREFIX_orders)
AND date_add < NOW() - INTERVAL 1 MONTH;

The PREFIX_connections and PREFIX_connections_source tables

These tables store information about customer visits, provided you’re using the statsdata module and have data collection enabled. If your store relies on Google Analytics, Matomo, or another analytics solution, you likely don’t need the data from these tables.

That said, it’s worth noting that sometimes third-party modules use this data — for example, some Google Tag Manager integration modules rely on these tables to determine the traffic source for conversions. Before changing the configuration of the statsdata module or cleaning these tables, it’s a good idea to check with a specialist to make sure you don’t actually need this data.

If you’re using Google Tag Manager modules that may depend on this information, it’s better to only remove records older than, say, 6 months. Below are two queries that will delete records older than 3 months.

DELETE FROM PREFIX_connections
WHERE date_add < NOW() - INTERVAL 3 MONTH;

DELETE FROM PREFIX_connections_source
WHERE date_add < NOW() - INTERVAL 3 MONTH;

The PREFIX_log and PREFIX_mail tables

These tables store data that may turn out to be important, but you certainly don’t need their entire history. I recommend archiving them from time to time. Here, you have two options: you can either clean out records older than a few months or completely clear them after archiving once in a while.

DELETE FROM PREFIX_log
WHERE date_add < NOW() - INTERVAL 3 MONTH;

DELETE FROM PREFIX_mail
WHERE date_add < NOW() - INTERVAL 3 MONTH;

Optimizing tables after cleaning

After cleaning up individual tables, it’s a good idea to run a command that will optimize them, for example:

OPTIMIZE TABLE PREFIX_connections, PREFIX_connections_source;

What’s next?

There are many modules available on the market that allow you to perform such cleanups in a simpler way. Remember to always run such modules on a store copy first, as they may cause data integrity issues.

One of the free modules that can be useful for cleaning up a PrestaShop database is PrestaClean, available on GitHub.

The queries mentioned in this article are worth running regularly — you might even consider creating a script that runs via a cron job on your server. Of course, these are just a few examples of tables that can be safely cleaned; this doesn’t cover fixing data integrity issues, removing ghost records in some places, outdated discounts, etc. Perhaps one day there will be an opportunity to write about that as well.