Techniques for Caching SQL Query Data

Question

Having read some on this subject:

http://stackoverflow.com/questions/37157/caching-mysql-queries

http://www.danga.com/memcached/

My SQL Caching problem: http://www.petefreitag.com/item/390.cfm

http://framework.zend.com/manual/en/zend.cache.html#zend.cache.introduction

I have a very unique (narrow) set of Queries and I think I could implement some caching quite easily within my current FastCGI C API executables (NOT PHP).

Zend describes their framework as: cache records are stored through backend adapters (File, Sqlite, Memcache...) through a flexible system of IDs and tags.

HOW is this implemented?

Since the same query can return different results if the Table has been changed, I need to monitor not only Queries, but UPDATE, INSERT and DELETE also (MySQL for now) Since this only happens from one of my processes, I could easily add a statement that deletes the cache when a table change is made.

Only SELECTs are permitted by the clients, in which case I could hash the queries and store them in a hash table or btree index along with a pointer to the file containing the results.

Is there a better way?

Answer 1

Oh man, that is a good question. Being a .NET developer, I have been fortunate enough to not have to worry about this at all over the last 7 years. I don't have to worry because .NET implements a quite powerful caching mechanism to do what you want to do.

There is no way to do this at a middle tier or presentation tier layer?

Answer 2

.NET is certainly convenient for fast development but it has some undesirable consequences as MS makes a deaf bedfellow!

I also prefer to develop me own solutions as I can tailor them to my needs. I don't need a lot fo sophistication, just some mechanism to tie inbound queries to results sets on the server side. The presentation is don on the client side where a seperate database exists. Requests are really only to update the client side database so in answer to your question, the client side caching is allready implemented.

Answer 3

As I think about this, I realize that allthough Caching results to disk is faster, the load on the disk will increase significantly. In my case the DB is not that slow for queries, it is the memory required for the result set that is the biggest issue, up to 3MB per result set. This will eat up server memory in no time If I keep it all in mempry while the xfer proceeds

Answer 4

Do I only get one shot at an answer?