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?