Affilistore 2.01 - turbocharged
There is no question that Affilistore 2.01 is a massive step forward in terms of the time it takes to generate a page, but is there more that can be done?
I have reviewed the code and determined that there is, and with one easy applied sql script through phpMyAdmin or whatever tool you use to run mysql directly.
The patch:
ALTER TABLE `affilist_products1` ADD INDEX `singleprodmerchantid`(`prodDB`, `merchantProdID`),
ADD INDEX `singleprodnomerchantid`(`prodDB`, `dbProdID`),
ADD INDEX `popularity`(`prodPopularity`);
Standard Affilistore uses the merchant database number (proddb) in conjunction with either the merchants own product id or, where you have not selected via admin that the merchant product id be used, it will fall back to use the row number in the feed (the dbProdID).
Unfortunately, Affilistore does not come with indexes to allow it to find the product without performing a full product table scan … barely noticeable if you just have a few hundred products, but very noticable if you have a catalogue with 100,000+ items.
The third index will help in fetching back the most popular items as it will allow mysql to quickly locate the biggest ‘prodpopularity’ numbers without the need to trawl through the entire database to find them. This only really kicks in however after there have been some visits to merchant and there really are popular products … also worth a note is that ‘prodpopularity’is reset to zero for products subject to a feed refresh.
I hope you like this modest hack. It certainly seems to make a difference. See http://www.tool-of-the-trade.co.uk which is running on bog standard shared hosting and serving 140,000 products. It isn’t necessarilly the fastest site, but the point is that it actually runs very well for Affilistore with that many products.
Why not subscribe to the feed now and watch out for any more tips, tricks and tools that I bring you.
Your quick comments RSS Feed
Hello
please tell me where i should add this code?
every time when i’l install AffiliStore?
Admin maybe do you know
How to add extra fields to FeaturedProduct and PopularProducts such as:
MERCHANT
PRODCAT
BRAND
PROMO - length up to 30 letters
EXTRAFIELDA
EXTRAFIELDC
EXTRAFIELDD
PRICE
Regards,
Alexey
@Alex,
You only need to run this code once when you install … the indexes remain in place and are automatically updated when you refresh the feeds.
@freshmobile.us
What you want to achieve should be very straightforward, but maybe cluttered?
Take Popular Products … and have a good look at popularprod.inc.php (the engine) and popularprod.tpl (the presentation template).
The data is fetched back in a mysql query near the top of the php file, but doesn’t include all the fields that you want …. simply add them.
These will then be added to the $popularlist array and can be referenced in the same way that prodname is referenced.
Second last step is to use $box_content->assign() to create the template tag and associated text.
Finally add the tag to the popularprod.tpl template.
I don’t want to give you exact code … you will have more fun and sense of achievement if you do it yourself.