I have a category page that lists all the products in a category. In addition, there is a side-navigation menu with a list of say 100 sub-categories along with an item count, kind of like this example:

  • Paintings (20 items)
  • Sculptures (2 items)
  • Carvings (4 items)
  • ...

On my navigation script, I go through a PHP loop of each sub-category within Art and count all of the products within those sub-categories. I have it set up where it records the timestamp so it doesn't count every time the page is loaded, only updates once per hour. Otherwise it will display the count stored in the database instead (which is quicker). The problem now is I have 50,000 items and this counting method is starting to get a bit slow.

My count script is currently like this within the sub-category loop:

Category "Art" is parent categorylevel1, while a sub-category like "Paintings" is categorylevel2. I also allow members to list a product in 2 different categories (category1 and category2):

PHP Code:
if ($timestamp 1 hour...) { //loops through 100 sub-categories to count products in each subcategory and store count in database.
$sqlcount "SELECT COUNT(*) as num FROM products WHERE (category1level2 = '$categorylevel2id' OR category2level2 = '$categorylevel2id')";
$result mysql_fetch_array(mysql_query($sqlcount));
$count $result['num'];
mysql_query("UPDATE categories SET count = '$count', timestamp = '$onehour' WHERE id = '$categorylevel2id'") or die(mysql_error());

Let me know if there is a better system. More efficient MySQL query? Cron Jobs?