how to optimize and speed up the database of your oscommerce-based store

There are a lot of things going on in the mysql database of an oscommerce-based store. Data is getting written into table rows, other data is getting updated, some deleted. Over time all of these operations leave behind old markers for rows, empty blocks that could be used but are being ‘avoided’ etc that can build up to the point of causing weird errors in the cart.

Optimization is the jargon for cleaning up the debris in the database so the read/write processes run more efficiently, giving you quicker query times and more accurate, trouble free store operations. It’s the server equivalent of defragmenting a disk drive of your computer.

The optimization process is simple and I’d recommend performing this regularly. Here’s how:

If you have a cPanel hosting interface, look for the link to the database utility phpmyadmin:

phpmyadmin

Select the database you want to work on, then check the size of what’s referred to as ‘overhead’ – ie junk that prevents the database from working as smoothly and efficiently as it could:

overhead figure

If there is some, remove it by selecting All Tables (1) and then from the dropdown in the middle, Optimize Tables (2) :

select all tables and optimize

After the procedure has completed, the overhead count on the lower right should show 0 bytes. Job done :

overhead removed

How often do you have to optimize a database?
I recommend every 3 – 4 weeks.

Anything else we should do when there?
Never hurts to run Analyse Table as well as this is like a wheel alignment for the indexes and keys used on tables.

I’d also recommend truncating (ie emptying) the frequently updated tables like whos online, visual_verify_code and sessions.

If you need help with this contact me or alternatively I can provide maintenance like this (and more) on a support contract for your site