Re: [fw-db] Caching SQL query results

Much of this can probably be done with page level caching, instead of caching the SQL Queries. MySQL can cache queries itself, as well.

On Thu, Jan 28, 2010 at 12:01 PM, Andreas Möller <localheinz@l8m.de> wrote:

* In an e-commerce context, how many SQL queries would you consider to
be too much, for example on a complex product page, with nested
categories, products and multiple references, collections of products,
multi-product kits, temporary events, promotions, and everything plus a
bit of AJAX all over it?

You mean, in a year?

Doesn't make any sense counting queries, if the total is not set in relation with a specified time.

* We have a lot of these SQL queries at the moment, and we're
considering enabling a SQL query result caching mecanism soon. But right
now, we're wondering where we're supposed to add this kind of system in
Zend_Db. There don't seem to be any useful hook around it (except
insert, update & delete). We didn't find where we should hook the system
because there's so much methods... find(), fetchRow(), fetchAll(),
findDependentRowset(), findManyToManyRowset() and so on... Moreover,
since it's not designed to handle ORDB, there don't seem to be any way
of catching IDs (or whatever column is used as an identifier) in the
queries. Which is kind of a problem if we want to use it as the cache
identifier.

Use

 [x] Zend_Cache_Manager

and prepare as many cache templates as you like, and then cache results with cache instances retrieved from there.

* On the other hand, we do know that MySQL already have some internal
caching mecanism, but the application still sends dozens of duplicated
requests to the server on the same page. We don't know if this cache is
of any help or it's just smoke and mirrors.

Maybe it helps taking a look at an ORM such as Doctrine?

I already crawled the web, searching for elegant solutions, but all I
found was some tips'n tricks that are of no use in our situation (static
caching and manual caching at each query). We really need to automate
the process because we're kinda building a massive application here.

How about caching at model level?


Best regards,

Andreas