Optimization & Caching solutions Anyone?
We've been thinking on and off about some sort of optimisation/caching solution for our flagship production site.
It's not that it receives a massive number of page impressions, just that many pages do feature hefty database queries (using MySQL 3.23.xx so no subquries here) and customers with 56k+ connections, using old machines (pre 2001 Macs for example) do often moan about the speed of the service (Oh good - the data-quality is OK then! :p)
I know about adding indexes to those tables with many rows (Of which our biggest table has something like 15-20,000) - although I've read that while this can yield query speed enhancements it also adds to overall server memory/cpu load. (This said we're due to transfer the site and it's LAMP setup to a very fast machine indeed... *rubs hands*)
I'd welcome your opinions, experiences and thoughts, with regard to caching mechanisms and code/db/server optimisaton solutions (of which The Zend Optimizer I think is one of the latter??)
Some of this is a little OT for sure, but I reckon it's best not to post the same topic in multiple forums.
Thank you all in advance for your thoughts.