It's ALWAYS a trade-off. And sometimes it's a trade-off that changes over time.
I wrote a program for a customer of mine that was a "people who like this also bought" feature. Basically on each of his web site's product detail pages, we'd get our current item number and cull through the orders from the last 30 days looking for items that were in orders with the current item. Ordered by frequency, decending, limit 3.
Worked great until he tripled the number of SKUS he carries. And then he got a big fat mention on a prominent TV show and his traffic and sales skyrocketed.
Suddenly each of his product detail pages take three minutes to load, as the database grinds under the load of all this data. So we back 30 days off to 10 days which helps a little. Now pages only take 60 seconds....
Ultimately, a rewrite was called for. I turned the script into a cron job that goes through the orders table at midnight or so, pre-caching the association data for each item in the database, and modified the detail page to do a simple query from the cache table. Shazam, instant product detail pages.
So in that case, my initial design was ALL about effectiveness. I didn't have a eye on efficiency at all, because I failed to anticipate the data would grow the way it did.
|