# 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:

```sql
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.

```sql
DELETE FROM PREFIX_connections
WHERE date_add < NOW() - INTERVAL 3 MONTH;
```
```sql
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.

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

```sql
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:

```sql
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**](https://github.com/SpiriitLabs/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.