If you ever added Output Queries Debug (http://www.oscommerce.com/community/contributions,2575)
to your site (and didn't do any caching) you will have noticed a sometimes substantial number of count queries like:
select count(*) as count from categories where parent_id = '1'
These are queries originating from the function tep_has_category_subcategories which is called in the function tep_show_category in catalog/includes/boxes/categories.php (the categories box) to determine whether a category has any subcategories. If yes, than it will print an arrow (->) behind the category name.
This mod will get the information in one query instead of using a separate count query for every category and subcategory shown in the categories box.