Compacting databases

[Lee.K]

My main database has grown in size due to excess items. After deleting the records for these no longer needed items (a couple hundred), I backed up the database and compacted. Unfortunately, the file size did not change at all after the compact command. How can I reduce the size of the file to get rid of the empty records?

My pages all access items via the SKU, not the item number, due to needs of the site and ease of updating. Since this method has to search the file (indexed field?), I've noticed a little slowdown entering items into the cart as the database grows so would like to figure this out.

Lee

[Jim]

How big is the database? What operating system is the store on? What version of ShopSite is the store running?

In addition to pages and products the database also contains orders, coupons, gift certificates, registered customers etc. So if you just deleted pages and/products that may not be what was taking up all the space.

The compact routine removes space taken up by deleted items but unless the file system the store is running on allows the file to be decreased in size, it may look like the same it is the same size.

[Lee.K]

ShopSite version: ShopSite® Pro 11 sp1 r2
Server: Apache 2.2.22 on FreeBSD 8.2

Very few customers bother registering (but I thought they're in their own database anyway), just a couple coupons, but it probably is all the orders that's taking up most of the space. I did think that removing a few hundred items would have shown up even as a small size change in the main database, but the sizes in the backup column and the active database column are always the same.

Main Database
(shopsite_db) --
Active: Tue Sep 04 11:52:14 2012 1982464 --
Backup: Sat Sep 01 17:06:52 2012 1982464

Thanks for the info.
Lee

[Jim]

That database is very small, not even 2 megabytes in size, and should cause no slow down in a store. (I have seen stores with databases over 200 meg that still function quickly.) The SKU field is indexed so looking up an item by sku should not be a problem.

Any slowdown in adding an item to the cart would not be caused by looking something up in the database. The add to cart form/link has all that is needed to add the item to the shopping cart. The database would not even be accessed unless you have inventory tracking enabled, in which case a check is made to see if there are enough items in stock for the order.

Things that might slow the cart loading are accessing a realtime shipping carrier like UPS, USPS, FedEx or a custom shipping API. Also if you have a custom tax API configured or use Avatax that might also take some time. Also if you have Google Analytics enabled it will have to contact the Google server to record that a transaction is inprocess. If you have other features (ads, etc) that load content from other sites that would also cause a delay.

Usually the biggest problem would be that the store is on a server that is overloaded. Some of the low cost hosts put thousands of accounts all on a single server. That much load could cause a problem but it would normally not take more than a couple of seconds to add an item to the cart.

[Lee.K]

Thanks for the info. The slowdown is not terrible in any way - only a second or two for the cart to appear after pressing the add to cart button. It just seems like it was instant when the database was smaller.

I am on a shared server (on a quality host - pair.com) but it has excellent response time and is rarely overloaded. I do use the realtime UPS quotes so maybe that's causing the small pause.