Delete customer carts older than date X (SQL help?)

[bodyjewelrystores]

Hi all,

Our web store has been open for almost a year, and over time the database has accumulated a lot of customer carts that were never checked out. I seems like this is a major factor slowing down our website (especially when it comes to adding items to cart, and checking out).

I'm looking for a way to clean up old, unused carts automatically, perhaps with an SQL query.

I'm not very well-versed in SQL... but it seems that these old cart items are stored in tables "customers_basket" and "customers_basket_attributes". But only the former one has a date field, and I'm not seeing a straightforward way of correlating to the correct rows in the second table.

So... does anyone have an idea? How can I automatically purge every non-checked-out cart that's older than date X?

(we're already using the "Recover Cart Sales" add-on, which allows me to review and delete old carts, but only one-by-one - and when it comes to our web server and the state of our database, this is impossibly slow. So I'm looking for a solution that just deletes everything older than a certain date).

Thanks in advance.

[data_digger]

Backup... I already drunk beer today... Beer + writing delete statements can give funny results ;)

DELETE FROM customers_basket
WHERE customers_basket_date_added < 20081231

Where 20081231 stands for 31th December of 2008 ( it will remove all carts created before that date).

After executing above statement, execute

DELETE FROM customers_basket_attributes cba
WHERE cba.customers_id NOT IN (SELECT cb.customers_id FROM customers_basket)

[bodyjewelrystores]

THANK YOU!!! Seems to work perfectly... see, no need to sully the good name of beer ;)

(Well... the second query did give me a syntax error... but I changed it as below and it worked):

DELETE FROM customers_basket_attributes 
WHERE customers_basket_attributes.customers_id NOT IN (SELECT customers_basket.customers_id FROM customers_basket);

Thanks again.... you've just saved me hours upon hours of work, so I'm gonna go get a cold one myself.

[G33K-Chik]

It didn't work for me.

I got the following error:

SQL query:
DELETE FROM customers_basket WHERE customers_basket_date_added <20081231
MySQL said:
#1146 - Table 'swansoninc_com.customers_basket' doesn't exist

Can someone help me? I have a ton of carts to get rid of and my site is so slow that I am losing sales because people are going to competitors instead.

I am using 1.3.7. www. swansoninc. com

[G33K-Chik]

Well, tried it again and it worked. Not the second code, but the first one....so yay! No more old carts.

[bodyjewelrystores]

For best results (and proper db maintenance), you should probably try the 2nd query too - otherwise, the old carts' item attributes will still remain as "orphaned" data, taking up database space for nothing and very possibly slowing it down...

Of course if your store does not have item attributes, feel free to ignore this :)

But otherwise, this should work:

DELETE FROM customers_basket_attributes WHERE customers_basket_attributes.customers_id NOT IN (SELECT customers_basket.customers_id FROM customers_basket);

[G33K-Chik]

Hmmm....

I got this when I ran it:

1146 Table 'swansoninc_com.customers_basket' doesn't exist
in:
[DELETE FROM zen_customers_basket_attributes WHERE customers_basket_attributes.customers_id NOT IN (SELECT customers_basket.customers_id FROM customers_basket);]

Oh, well. I got rid of all those pesky old carts and my site didn't crash. I'm happy.

[bodyjewelrystores]

Yeah I'm not sure why that weird prefix keeps adding itself to your table names when the query is processed... especially if your installation doesn't use table prefixes (which seems to be the case, otherwise that table name would've existed)

Wonder if you're running this code from Zen's SQL patcher, or from phpMyAdmin... if one doesn't work you could try the other... No big deal though, as long as the site didn't crash, I guess. :)

[G33K-Chik]

I tried it from both. Got the same error message in both places. Oh well....I'll just blame my host for changing the prefix. haha

[DogTags]

After you cleared lots of old carts, did you notice a performance boost?

Also, is there a way to combine the two SQLs above so they cover both concerns mentioned?

Thanks