Safe Database Pruning/Maintenance

[Darkmuncan]

Hi, I've been using ZC for almost a year now, very happy with its performance and the custom features I've been able to add. I wanted to get some advice from some of the pros around here as to which tables within the database may be best to prune to possibly help with loading and what not and also any recommendations as to how to best do it in a safe way. Currently the db is around 7MB with the primary tables with the largest amounts being 'zen_orders', 'zen_orders_products', 'zen_customers', 'zen_admin_activity_log', and the biggest 'zen_sessions'. What do each of these control and is there a good way to prune them, should i only delete old entries, will this affect sales data etc? Any warnings?

Thanks for any help you can provide.

BTW, don't be fooled, even though my forum title says 'New Zenner' I actually have been on here quite a bit, mostly reading others questions.

[wickedklown]

i dont believe that anyone here is going to tell you that its OK to prune your database directly.

You could ruin the whole thing just by messing with one field inside the database. removing/adding/pruning, anything of the such- should be removed by way of the admin console within zencart. any other way could be asking for trouble.
I believe that there is a control for anything inside the database that needs to be pruned. so that you can do it safely without risking a broken cart in return.

The admin log, can be removed with STORE MANAGER option. archived emails can be removed with the "Email Archive Search" modual from Blindside (i believe). Your archive of general logs should be located inside the log folder. FTP to the domain and clear it if needed. Otherwise using the "USER TRACKING SYSTEM" modual will remove these by set dates if you wish...

im sure you know how to prune your customer database, and i hope that your not removing your old orders, as you should always have some record to refer to if needed. *Hardcopy of/or virtual*

[Darkmuncan]

Thanks clown, I don't go deleting things willy nilly in my database, just curious if there's a quick way to do it safely. I usually empty the admin_activity_log table directly in PHPMyAdmin every few weeks or so since it quickly builds. I've considered developing a program in Access that I can use to open/view/run reports with on database backups stored locally so I could trim the ones actually being used online. I think we may trim customer orders table to contain only the ones from this year, mainly because we already have the sales data in our inhouse software and customers generally don't need to view order history from last year. If they do they can contact our customer service who can easily acces the info in our inhouse software. I'm still a bit on the cautious side though so I'm not going to do anything until I hear from a few others like yourself who probably know a bit more than myself.

[Kim]

The sessions table can generally be emptied without problems.

[wickedklown]

Thanks clown, I don't go deleting things willy nilly in my database, just curious if there's a quick way to do it safely. I usually empty the admin_activity_log table directly in PHPMyAdmin every few weeks or so since it quickly builds. I've considered developing a program in Access that I can use to open/view/run reports with on database backups stored locally so I could trim the ones actually being used online. I think we may trim customer orders table to contain only the ones from this year, mainly because we already have the sales data in our inhouse software and customers generally don't need to view order history from last year. If they do they can contact our customer service who can easily acces the info in our inhouse software. I'm still a bit on the cautious side though so I'm not going to do anything until I hear from a few others like yourself who probably know a bit more than myself.

You can empty that part of your log right through the admin console of zencart.
you should be able to use the TOOLS>STORE MANAGER
the first lines read:

Empty Admin Activity Log table from the database
WARNING: Be sure to backup your database before running this update!
The Admin Activity Log is a tracking method that records activity in the Admin. Due to its nature it can become very large, very fast and does need to be cleaned out from time to time.
Warnings are given at 50,000 records or 60 days, which ever happens first.

Here you can empty that without having to do other steps to make it happen..

also the admin console will notify you when that table needs attention... (last line reads....)

That could make it easyer for you.
Yes if you keep inhouse records as we do, then removing last years orders are fine... just make sure to have some type of system that can bring them up for furture reference. keep in mind that customers using there credit card have 60days+ to issue a complant to the CC company.... sometimes they can do it a year later. depending on the card. AM-EX and us went rounds with this exact issue last year. just some FYI

[Darkmuncan]

Klown, Thanks for the advice regarding cc disputes, we generally can keep track of all that through our payment gateway as well so I think it should be ok.

Kim, is the sessions table just containing information like customer cart contents of customers who didn't login and what not? What will I lose if I emtpy this table?

[wickedklown]

Klown, Thanks for the advice regarding cc disputes, we generally can keep track of all that through our payment gateway as well so I think it should be ok.

Kim, is the sessions table just containing information like customer cart contents of customers who didn't login and what not? What will I lose if I emtpy this table?

sounds like you should download the user tracking modual.

this will not only allow you to keep track of each session created in detail (clicks/cart/reffering URL, ect.ect) but you can prune it down as well with a simple click.

we use this for many things- i would recomend loading it up and taking a look. I think youll find it even more helpfull than i can explane in short detail.

Good deal with your payment gateway- depending on what you use, they only hold the data for so long... best if your keeping your own records.. especialy when dealing the infamous Paypal, also make sure to keep a leger for shipping costs/locations/tracking/ect. this is what will help you when it ever comes down to your word and the customers.... (HINT- they arnt ALWAYS right.. just most the time :)

[Darkmuncan]

Well I use that module actually, I can see who's online and what page their on, items in cart, etc. Is this information what gets stored in that sessions table? If so then it doesn't seem to be important to store it for much longer than a week or so. I must have an older version though because I've never seen any place in it that allows me to empty those.

Oh and we've been running this business for over 3 years now, online only. We have had at one point over 60 employees, doing $2 million sales yearly currently. So we have a whole call center, internal custom built software, the works. We've always been able to work with our merchant account providers and customers to work through disputes. Since we cut out a lot of our manual processes and automated everything we hardly even get disputes anymore because we make less errors. Oh and when we first started we used Paypal exclusively, got away from that quickly since we were having people dispute stuff all the time; lousy Paypal almost always rules in favor of the customer disputing whether their claim is valid or not.