Clean PrestaShop database - Drop old and unless data

clean-prestashop-db.sql

# Delete all logs
TRUNCATE ps_log;


# Delete old connection data (only used for stats)
# change 2016-02-01 00:00:00 according to you needs
DELETE c, cs
  FROM ps_connections c
  LEFT JOIN ps_connections_source cs ON (c.id_connections = cs.id_connections)
    WHERE c.date_add < '2016-02-01 00:00:00';
    
OPTIMIZE TABLE ps_connections, ps_connections_source;


# Delete all guest without entry in ps_customer table
DELETE g
 FROM ps_guest g
 LEFT JOIN ps_customer c ON (g.id_customer = c.id_customer)
   WHERE c.id_customer IS NULL;
     
OPTIMIZE TABLE ps_guest;


# Delete tables
# Scenes are deprecated in 1.6 (used only if upgrading with feature active)
DROP TABLE `ps_scene`;
DROP TABLE `ps_scene_category`;
DROP TABLE `ps_scene_lang`;
TRUNCATE `ps_scene_products`;
DROP TABLE `ps_scene_shop`;
UPDATE `ps_configuration` SET value='0', date_upd=NOW() WHERE `name` = 'PS_SCENE_FEATURE_ACTIVE';

Please consider and verify my proposal:

# 404 erors
DELETE FROM `ps_pagenotfound` WHERE `date_add` < 'YYYY-MM-DD' ;
# Sent mails
DELETE FROM `ps_mail` WHERE `date_add` < 'YYYY-MM-DD';
# Searched keywors ???
DELETE FROM `ps_statssearch` WHERE `date_add` < 'YYYY-MM-DD';
# Connection page (ps_connections_page.id_connections - ps_connections.id_connections)?
DELETE FROM ps_connections_page;

# Other "suspected" tables but i'm not sure
ps_search_index
ps_page_viewed
ps_search_word

Thanks for sharing your cleanup code, but I'm concerned about deleting the entire guest tabel.

ps_guest is as far i can see connected to ps_cart for customer that have not yet logged in or checked out. So when you delete eveything in ps_guests all visitors will lose their saved carts. if they have not yet completed the purchase.

When using pscleaner module, it would remove carts that is more than a month old, and is not in ps_order:

DELETE FROM `'._DB_PREFIX_.'cart`
		WHERE id_cart NOT IN (SELECT id_cart FROM `'._DB_PREFIX_.'orders`)
		AND date_add < "'.pSQL(date('Y-m-d', strtotime('-1 month'))).'"';

This leaves a lot of ps_guests without a matching ps_cart, so what im doing after is running the following code

DELETE g FROM ps_guest as g
LEFT JOIN ps_cart as c ON g.id_guest = c.id_guest
WHERE id_cart IS NULL

It still cleans up a lot of old guest data, in our case we got from 2.407.370 rows down to 5.015 rows and still let potential customers have their cart that has not yet been deleted by the cleanup module.

Do these not work on some versions of mysql/mariadb? All of these queries that contain both DELETE and LEFT JOIN error out with:

Unrecognized keyword. (near LEFT JOIN)
Unrecognized statement type. (near LEFT JOIN)

Do these not work on some versions of mysql/mariadb? All of these queries that contain both DELETE and LEFT JOIN error out with:

Unrecognized keyword. (near LEFT JOIN)
Unrecognized statement type. (near LEFT JOIN)

I have the same problem.

curious, will deleting old carts of COMPLETED orders cause an error 500 opening the order should it ever be reviewed? Someone just asked me to help them remove all carts over 1 yr old.