Useful SQL Queries

Sales

Find all expired quotes due for cleanup

SELECT * FROM quote WHERE is_active = 0 AND updated_at < CURRENT_DATE() - INTERVAL 30 DAY;

This is based on the default Magento quote lifetime of 30 days, this can be tweaked to make the result more aggressive but long standing sessions will lose their carts, this can be mitigated by checking for cart items on the quote.

SELECT * FROM quote WHERE is_active = 0 AND items_count = 0 AND updated_at < CURRENT_DATE() - INTERVAL 5 DAY;

This means customers do not lose any data and the carts still get cleaned making it safer to lower the interval (as above). Obviously, update the SELECT * FROM to DELETE FROM with the same conditions to perform the clean up.

Constructed from \Magento\Sales\Cron\CleanExpiredQuotes::execute

Sessions

Find all sessions that have expired but still exist in a session table

This query will highlight cron issues with the session clean up job.

-- Sessions that shouldn't exist
SELECT COUNT(*) FROM session WHERE session_expires < UNIX_TIMESTAMP(NOW());

-- Sessions that should have expired a month ago
SELECT COUNT(*) FROM session WHERE session_expires < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)

To clear out the expired sessions, the following query should do the job, a limit of 5000 is used to prevent the server being overloaded, the script should be put on a ~5-10 minute cron to keep things in check.

DELETE FROM session WHERE session_expires < UNIX_TIMESTAMP(NOW()) LIMIT 5000;