what should I do if I want get rid of old orders, which I dont need any more...?
When I simply delete them, all items from that orders get back to stock, which is unwanted. If my goal is to lower the database size on one side and simply delete old orders on other side, Is there any kind of "archiving" or deleting orders without affecting stock?
Just curious how big your store is? Generally, deleting old orders is not needed to maintain performance.
Be sure to change that code back after you complete your current deletion effort so that inventory will continue to be handled properly.
Actually, I must admit that I am really referring to our main website that is still using csc v2.
Currently in the process of creating the replacement on v4 platform.
Does v4 csc handle the database differently , speeding up the loading of orders list?
With v2, it takes 62seconds to load All 5-6 years of orders, so we delete orders more 5years old.
We do set up filters to speed this up, like last 30 days etc, but sometimes we need to check a customers previous order, while on phone to them, so we need it as fast as possible.. another reason for deleting old orders.
v4 will be faster on the new vps server, but its also pointless keeping the old orders in the database,
So to adjust inventory automatically when deleting 1000's orders is a disaster for inventory control.
How do you load 5 years of orders in the admin UI? Don't you have the number of items per page set to something <= 100?
It shouldn't take that long under V2. A search for a specific order_id should be very quick. If you are looking it up by email, it can take a bit longer since the email field isn't indexed (though it could easily be if that's something you do frequently).
I have about 20000 orders and about 5-10000 is old enough I would never need them. On other side size of order_data database is biggest in eshop 160-200MB and I have some hosting limitations for whole eshop. All the time Im balancing on 80-90% of its kapacity.
Anyway this deleting should be handled via special eshop feature as deleting can (I belive) affect more eshop features (statistics, customer order history, reward points, etc.)
Using the Search/Filter is faster, and we set up filters for each financial year, and a 30 day filter, and a few others, Generally speed is not an issue, its just on a few occasions where we want to load "all" that it becomes an issue.
Note that setting the "items per page" to <=100 has no effect on speed what so ever.
Sorry, I'm still confused by what you mean when you say "load all".
Note that the browser can be a huge constraint on large data sets that have select elements and/or complex html elements. It simply takes time to render and that might have nothing to do with your database. However, if you are frequently looking up orders by email address, then you might want to add an index for 'email' to the cscart_orders table. In V4 it doesn't appear to be indexed. It should be of type BTREE and NOT unique. This should also help when searching for orders by partial email address too since the whole orders table won't have to be scanned, only the index.
Timestamp is already indexed so you should see reasonable speed for anything related to order date.
The indexes of the cscart_orders table seems to be the same between V2 and V4.
For the csc v2 store, the slow order loading is mostly an issue if loading all orders (per-page <100 has no affect).
By "load all" I mean retrieve all orders from the database, as in "View Orders" or the "All" filter.
We have filters set up to search by financial year, so its quite fast to load at the beginning, but at EOFY around june, it gets slow to load. For day to day operation we have a filter that shows current orders only, those that require shipping or other attention. These filters load the orders list quite quickly.
It was a lot slower than it is now, changing to a faster mySQL server sped things up considerably.
Its not a browser issue.
Anyway, Im leaving this topic now as I am actually referring to our v2 store. The v4 store is on a different server.
In AU, we need to keep tax records for 5 financial years , so every 01-July we will delete 12 months of orders to save data space and speed orders retrieval from the database. We always keep .sql copy anyway.
We don't want 12 months of inventory added back in the process. Conversely, unlike some EU countries, we do not need to keep copies of cancelled orders/invoices, thus for recent orders that we cancel, we actually DO want the inventory increased as we delete the cancelled order(s). I dont think its a problem if we remember to set the orders to "cancelled" before deleteing them.
So, some cart admins will want the inventory added back and others will not.
Thus there seems to be a need for a Admin General>Settings for "Update inventory on order deletion: Yes,No, Ask"
A popup window asking if we want to update inventory as we confirm order deletion is needed?
I believe when you change the order status to cancelled that inventory is adjusted then so it woudn't be an issue when you delete the orders.
You could extend your my_changes addon to create a function that will delete the order data without modifying inventory. There are several tables where you would simply delete the related rows but not use the busiiness logic in the fn_delete_order() function.
Give a shout and I'll cut you a good deal. You can then just execute a single url once a year and it will leave only orders in the last 5 years from the date it is run or it could be specified to be something like "last Jan 1 - 5 years"/
Thank you, I had to upload the fn.cart.php file with your line commented out, and then do a graceful restart of Litespeed.
I'd advise anyone trying this to edit the fn.cart.php file and thendelete one order first. Choose an order with minimal products and note the qtys you currently have. Its handy to have a product with the current qty (that matches a product in your order) in another browser tab.
Then delete the single order, refresh your product page and make sure none of the qtys increased by 1. If thats the case, you can then go through and delete multiple orders at a time.