After installing version 4.1 on our public web site after having tested it on our test site I discovered that the dynamic site map was taking nearly 30 seconds to load, and we are directly connected to our web server using gigabit ethernet! Since our public site currently has over 10,000 different products spread among 319 different categories that are nested up to five levels deep I suspected that counting the products in each category to determine which ones to hide was a part of the problem. Sure enough if I commented out the product counts for the categories the time to display the page was cut at least in half.

A quick look at the product counting function revealed that it performs one count query for the category plus one for every subcategory it contains. A category with ten subcategories would therefore perform eleven counting queries just to get the number of products for that one category. I have therefore rewritten the category product count methods in both dynamic_sitemap.php and create_xml_sitemaps.php so that it first builds a list of the needed categories and then performs ONE count query on products found in that list of categories. This keeps the time to display the dynamic site map on our web site down to a much more acceptible level. I also discovered that another five seconds could be saved simply by adding an index to the manufacturers_id in the products table.

The complete install is attached but only two files have changed since version 4.1. Complete upgrade and install instructions are included.

More...