Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Clean PrestaShop database - Drop old and unless data
# 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';
@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Feb 11, 2018

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
@Casper-O

This comment has been minimized.

Copy link

@Casper-O Casper-O commented Jun 4, 2018

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.

@ingenium13

This comment has been minimized.

Copy link

@ingenium13 ingenium13 commented Jun 20, 2018

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)
@Skinperforator

This comment has been minimized.

Copy link

@Skinperforator Skinperforator commented Feb 11, 2019

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment