Deleting old order records and customers

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

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
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.

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.

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.

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