Deleting old order records and customers

[devus]

I've been running zen-cart for a few years and the database has grown larger than my hosting provider will automatically back up or allow me to import.

I would like to delete old order records from years ago, and maybe customers who have not ordered for years.

Can I just delete them via PHPMyAdmin or will this mess up lots of database linkages?

Is there anything else I can delete from the database that isn't of use, like old log entries. I do clear up the stuff in Store Manager regularly already.

Thanks in advance,

Chris

[lat9]

I've been running zen-cart for a few years and the database has grown larger than my hosting provider will automatically back up or allow me to import.

I would like to delete old order records from years ago, and maybe customers who have not ordered for years.

Can I just delete them via PHPMyAdmin or will this mess up lots of database linkages?

Is there anything else I can delete from the database that isn't of use, like old log entries. I do clear up the stuff in Store Manager regularly already.

Thanks in advance,

Chris

First, make a backup of your current database.

Do not use phpmyadmin to remove/delete the records! There are many intertwined entries for a customer and an order; you should use your Zen Cart admin's Customers->Customers and Customers->Orders to remove the customers and orders, respectively, one at a time. It could be time-consuming, but going through the process that way will make sure that your database is intact when the process is completed.

[DrByte]

Deleting customers and orders will probably not reclaim all that much space.
But pruning the Admin Activity Log (after a backup) and the Email Archive (if you've got it enabled) will clear up a LOT of space.
Same with cleaning out old .log files from your /cache/ and /includes/modules/payment/paypal/logs/ folders.
And deleting old image files you're no longer using.

[data_digger]

Here's method to find out which tables actually take most space. Execute the query in phpmyadmin, it'll return list of tables sorted by their size in descending order:

SELECT ( (t.DATA_LENGTH + t.INDEX_LENGTH)/(1024*1024)) as 'total_size_mb', t.*
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_SCHEMA LIKE 'name_of_your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC

If Your database takes less than few gigabytes I would look for another hosting company.

[data_digger]

I forgot to mention, that chances are that You're allowed only allowed to view Your schemas portion of the INFORMATION_SCHEMA database and You don't have to put even 'name_of_your_database'. Just copy & paste & execute in phpmyadmin:

SELECT ( (t.DATA_LENGTH + t.INDEX_LENGTH)/(1024*1024)) as 'total_size_mb', t.*
FROM INFORMATION_SCHEMA.TABLES t
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC