Most efficient way to count items under a nested tree

Question

So I'm looking to provide a category (x) style product count on my website.

I'm currently using MySQL.

My categories table looks like: Categories (Id, TreeLeft, TreeRight, Level, Name) - with 'Level' being the node depth.

Categories entered look like this:

My Item > Categories relation table looks like: ItemCategories (ItemId, CategoryId)

Assuming I have:

• 1 item under 'TUBE'
• 2 items under 'LCD'
• 1 item under 'FLASH'
• 1 item under '2 WAY-RADIOs'

How can I most efficiently query my items (large db) + categories (4000 in db), to produce:

Electronics (5) 
 - Televisions (3)
   - Tube (1)
   - LCD (2)
 - Portable Electronics (2)
   - MP3 Players (1)
    - Flash (1)
   - 2 Way Radios (1)

Taking note to only return those categories which have products in them and also correctly counts them up the tree.

Any help most appreciated.

Edit: DB Code to recreate environment locally:

CREATE TABLE IF NOT EXISTS `Categories` (
  `Id` int(11) NOT NULL auto_increment,
  `TreeLeft` mediumint(7) NOT NULL,
  `TreeRight` mediumint(7) NOT NULL,
  `Level` tinyint(3) NOT NULL,
  `Name` varchar(255) NOT NULL,
  UNIQUE KEY `Id` (`Id`),
  KEY `TreeLeft` (`TreeLeft`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

INSERT INTO `Categories` (`Id`, `TreeLeft`, `TreeRight`, `Level`, `Name`) VALUES
(1, 1, 20, 1, 'Electronics'),
(2, 2, 9, 2, 'Television'),
(3, 10, 19, 2, 'Portable Electronics'),
(4, 3, 4, 3, 'Tube'),
(5, 5, 6, 3, 'LCD'),
(6, 7, 8, 3, 'Plasma'),
(7, 11, 14, 3, 'MP3 Players'),
(8, 15, 16, 3, 'CD Players'),
(9, 11, 14, 3, '2 Way Radios'),
(10, 12, 13, 4, 'Flash');

CREATE TABLE IF NOT EXISTS `ItemCategories` (
  `CategoryId` int(11) NOT NULL,
  `ItemId` int(11) NOT NULL,
  KEY `CategoryId` (`CategoryId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `ItemCategories` (`CategoryId`, `ItemId`) VALUES
(4, 3442),
(5, 3441),
(5, 3456),
(9, 5343),
(10, 5423);

Answer 1

Please see the subsection, entitled Aggregate Functions in a Nested Set, of Mike Hillyer's classic article on this subject at Managing Hierarchical Data in MySQL