Many sessions and carts, slow to delete

We have a Cartthrob (9.0.0) store running on EE 7.5.17. The store is behind a members only area and currently has no items for sale on it. Our members are not actively using the store.

We are seeing a large number of sessions and carts being generated (171,000+ DB rows at this time). For example, here is a typical snapshot of the timestamp field from exp_cartthrob_cart:

| 2025-12-01 11:44:44 |
| 2025-12-01 11:44:44 |
| 2025-12-01 11:44:44 |
| 2025-12-01 11:44:44 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:45 |
| 2025-12-01 11:44:46 |
| 2025-12-01 11:44:46 |
| 2025-12-01 11:44:46 |
| 2025-12-01 11:44:46 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:47 |
| 2025-12-01 11:44:48 |
| 2025-12-01 11:44:48 |
| 2025-12-01 11:44:48 |
| 2025-12-01 11:44:48 |

These are fairly low everhead to create, but still, why are they being created when we have no {exp:cartthrob} things in public templates?

The more worrisome issue is this mysql query:

DELETE `exp_cartthrob_cart`
FROM `exp_cartthrob_cart`
LEFT OUTER JOIN `exp_cartthrob_sessions`
ON `exp_cartthrob_cart`.`id` = `exp_cartthrob_sessions`.`cart_id`
WHERE `exp_cartthrob_sessions`.`cart_id` IS NULL;

This appears to run several times a minute, and each run is taking 2-5 seconds. Here is a partial from our slow query log:

# User@Host: [] @ localhost []
# Thread_id: 4178 Schema: database QC_hit: No
# Query_time: 3.889407 Lock_time: 0.003555 Rows_sent: 0 Rows_examined: 339764
# Rows_affected: 0 Bytes_sent: 11
# Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
#
# explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
# explain: 1 SIMPLE exp_cartthrob_cart ALL NULL NULL NULL NULL 169773 169882.00 100.00 100.00
# explain: 1 SIMPLE exp_cartthrob_sessions ref cart_id cart_id 5 database.exp_cartthrob_cart.id 1 1.00 100.00 100.00 Using where; Using index
#
SET timestamp=1764622401;
DELETE `exp_cartthrob_cart`
FROM `exp_cartthrob_cart`
LEFT OUTER JOIN `exp_cartthrob_sessions`
ON `exp_cartthrob_cart`.`id` = `exp_cartthrob_sessions`.`cart_id`
WHERE `exp_cartthrob_sessions`.`cart_id` IS NULL;

There appear to be a few issues here:

1. Why are carts/sessions being created if no-one is visiting the store?
2. Using explain we see the keys from exp_cartthrob_cart are being ignored:

explain DELETE FROM exp_cartthrob_cart WHERE NOT EXISTS ( SELECT 1 FROM exp_cartthrob_sessions WHERE exp_cartthrob_sessions.cart_id = exp_cartthrob_cart.id );
+------+--------------------+------------------------+------+---------------+---------+---------+--------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------------+------+---------------+---------+---------+--------------------------------------+--------+-------------+
| 1 | PRIMARY | exp_cartthrob_cart | ALL | NULL | NULL | NULL | NULL | 170753 | Using where |
| 2 | DEPENDENT SUBQUERY | exp_cartthrob_sessions | ref | cart_id | cart_id | 5 | khronos_org_ee.exp_cartthrob_cart.id | 1 | Using index |
+------+--------------------+------------------------+------+---------------+---------+---------+--------------------------------------+--------+-------------+


Altering the query slightly we can resolve part of the issue:
explain DELETE `exp_cartthrob_cart` FROM `exp_cartthrob_cart` LEFT OUTER JOIN `exp_cartthrob_sessions` ON `exp_cartthrob_cart`.`id` = `exp_cartthrob_sessions`.`cart_id` WHERE `exp_cartthrob_sessions`.`cart_id` IS NULL and exp_cartthrob_cart.id > 0;
+------+-------------+------------------------+-------+---------------+---------+---------+--------------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------------------+-------+---------------+---------+---------+--------------------------------------+-------+--------------------------+
| 1 | SIMPLE | exp_cartthrob_cart | range | PRIMARY | PRIMARY | 4 | NULL | 85434 | Using where |
| 1 | SIMPLE | exp_cartthrob_sessions | ref | cart_id | cart_id | 5 | khronos_org_ee.exp_cartthrob_cart.id | 1 | Using where; Using index |
+------+-------------+------------------------+-------+---------------+---------+---------+--------------------------------------+-------+--------------------------+

Side note: exp_cartthrob_sessions.cart_id is set as INT(10) SIGNED, not UNSIGNED.

To help resolve our current load generated from the delete query, can we flush the cart and session manually or from a cron job without breaking anything too badly?