PrestaShop clean MySQL database tables
On a PrestaShop site, queries like the following get stuck forever
SELECT c.id_guest, c.ip_address, c.date_add, c.http_referer, "-" as page FROM `ps_connections` c INNER JOIN `ps_guest` g ON c.id_guest = g.id_guest WHERE (g.id_customer IS NULL OR g.id_customer = 0) AND c.id_shop IN (1) AND TIME_TO_SEC(TIMEDIFF('2022-08-14 21:31:00', c.`date_add`)) < 900 AND c.ip_address NOT IN (1522500673,858191475) ORDER BY c.date_add DESC;
This is because PrestaShop database table ps_connections grow too large. This table had 6,67,557 records. This table record every visit to the website.
I removed entries from the table and a few other tables to fix it.
TRUNCATE TABLE ps_connections; TRUNCATE TABLE ps_connections_page; TRUNCATE TABLE ps_connections_source; TRUNCATE TABLE ps_pagenotfound; TRUNCATE TABLE ps_statssearch;
See PrestaShop