Here’s list of not so obvious tips for Zen Cart/osCommerce and probably most osCommerce clones:

  1. Get rid of products_viewed stats gathered in products_description table,
  2. Change storage engine of whos_online, counter and sessions tables to MEMORY,
  3. If You have thousands of images take a closer look on time needed to open folder with those images. Zen Cart open directory with images quite often and it’s quite probable that it takes considerable amount of time. Opening directory with few thousand images may even take few seconds. Solution: use tree structure of directories (more on that soon… or someday šŸ˜‰

Get rid of products_viewed stats to enable query cache

Zen Cart updates products_description table EACH time any product is viewed. It causes native MySQL query cache to NOT cache ANY queries regarding products (well, almost). Also it causes a lot of writes to database and it may slow down Your database server as much as 10-30% (real life scenario!). Just edit

/includes/modules/pages/product_info/main_template_vars.php

and remove the line with the update ( it should be line 39-42)

$sql = "update " . TABLE_PRODUCTS_DESCRIPTION . "
            set        products_viewed = products_viewed+1
            where      products_id = '" . (int)$_GET['products_id'] . "'
            and        language_id = '" . (int)$_SESSION['languages_id'] . "'";

Change engine of whos_online, counter, sessions tables to MEMORY

Change storage engine of whos_online, counter tablesĀ and sessions (requires Percona MySQL due to MEDIUMBLOB field in sessions table) tables to MEMORY storage engine. Again, it can save You up to 30% of database server load. Yeah, data is lost on server shutdown but who cares? You won’t loose anything important or anything that is not lost anyway (customers will leave Your site anyway if Your database server is gone).