Query caching on table level

[eluser]Volder[/eluser]
Hello.

I am developing a site based on CI. And there are a lot of dynamic content which is received through database queries.

Now I would like to make it use caching.
I have checked both possible cache modules natively present in CI:
- Web page caching
- Database Caching Class

but both of them are not what I'm looking for.

The first is storing cache in dimensions of controllers - which is not helping me as same queries are done in a lot of different controllers (e.g. tag cloud, last comments panel etc). Also the parametrization there is in minutes - how long the page be kept in cached state. And for example, if the user is publishing a new topic - he won't see it immediately published, but only after the cache expire.

The second caching solution provided - database queries - is not acceptable for me because:
the cache files stored are again for controllers and functions, but I need it depending on database tables. Because otherwise It's just a headache to understand what should expire when one table is updated.

How I see the perfect scenario:

the cache is stored for queries and expires when any DML (insert, update, delete) is done on any table inside query.

So the cache files to be stored in separate folders which are generated as names of all tables inside the query:
table1+table2+...+tableN and the names of the files are for example the hash of the query text.

And when any data changed in table2, for example, all the cache files which used this table - have to be marked as expired (=deleted).

I have searched a lot and read a lot in the forum and only one solution I found more or less acceptable is the Improved Query Caching. The code files and more description are here.

What are the advantages of this for me (although I have not yet tested this):
- it gives almost what I wanted - the caching technique for tables.

What are the negative points:
- as I used almost everywhere binding queries (not active record) - I need to mark table names to cache before every query, and mark what cached tables to delete after every modification of data (as it is marked - there is no automatic query parsing done);
- the second thing - as we can not change database classes - I need to rewrite the core of the CI, so I would need to do it after every renewal of the CI version. As the code was provided almost a year ago and CodeIgniter has improved several versions from that time - probably something is already missing there.


So can anybody give me an advice of how to organize database query caching with CI? I'm sure someone has faced similar requirments.
Any help is appreciated as I am trying to understand in which direction to move now.

[eluser]TheFuzzy0ne[/eluser]
Hello.

I'm not sure I agree with your "perfect scenario". It would mean that you would have put the table names in your query in the exact same order (unless your cacher put the table names in alphabetical order).

Is there any reason why you can't have your model clear the cache for a particular query when new data is inserted? I wouldn't have thought it would be too difficult to achieve.

[eluser]Volder[/eluser]
when I was using 'perfect' - I mean perfect for me.

regarding the unique table names - yes I was thinking about the same idea - like alphabetic order - just not to have redundant folder for the queries using same tables but in different order, like: table1+table2 and table2+table1, but to have only table1+table2 for example with both query cache files inside.

But on the other hand - there is no problem if we create 2 separate folders.
so if there is no alphabetic cleaning there would be done - then cache would be checked in folders which have the order, same as in quiery, that means if the same query is run again - his cache would be found.
This is just a question of implemenation.

The main idea, that when I do an update, insert, delete from table2 - all cached files using this table should be eliminated: table1+table2 and table2+table1 - if they would be stored in different folders.

regarding your second phraze:
"Is there any reason why you can’t have your model clear the cache for a particular query when new data is inserted?"

I don't fully understand - are talking here about native database caching class inside CI?
can you, please, give more details on this idea?

[eluser]TheFuzzy0ne[/eluser]
I don't know a great deal about the database caching class, but I'd assume it hashes the query, and uses that as the filename for the cache file. You can also do the same to obtain the filename for any given cache file, and thus delete it.

[eluser]Volder[/eluser]
this is the main problem which I'm facing.

The native DB cache class stores file for every cached queries, but in pages structure:

CodeIgniter places the result of EACH query into its own cache file. Sets of cache files are further organized into sub-folders corresponding to your controller functions. To be precise, the sub-folders are named identically to the first two segments of your URI (the controller class name and function name).

and following:

For example, let's say you have a blog that allows user commenting. Whenever a new comment is submitted you'll want to delete the cache files associated with the controller function that serves up your comments.

but imagine my comments queries are used in a bunch of controllers. And at the same time my controller which uses comments quiery - uses a lot of other quiries which are no how connected to comments - but I have to delete it.

What I would like - that caching would be organized on table level. So when I update Comments table - I want to clear cache only for queries which used this table.

Hope now the idea of what I need is more clear.
Actually, I was very surprised that this is not implemented natively in CI yet.

[eluser]Jelmer[/eluser]
You could try my MP_Cache library and have your front-end write & models delete your cache

In the case of a blog you would create the cache on demand to a file like:
/cache/blogs/25.cache (25 being the id of your entry)

Which you could write like this:

Code:

$this->mp_cache->write($blog_contents, 'blogs/'.$blog_contents['id'])

You could save your comments within the above file or save them seperately to a file like:
/cache/blog_comments/25.cache (25 being the id of the entry, not the comments)

On every update of (for example) your comments you could delete the current comment cache like this:

Code:

$this->mp_cache->delete('blog_comments/25');

Or delete all the comments:

Code:

$this->mp_cache->delete_all('blog_comments');

Please note that at this point the expiration is only supported for arrays, I'm working on an improved version which allows for cache-dependencies. You can find the work-in-progress of the second version on http://mpsimple.mijnpraktijk.com/mp_cache.htm.

[eluser]Jelmer[/eluser]
Oops, added this response by accident.

[eluser]Volder[/eluser]
Jelmer, thanks for your advice.

Actually while doing a search on the forum - I, of course, met your library.
And it does not meet what I need (or maybe I don't understand it fully).

According to description you provided in wiki - you need to create manually cache files after every query you want to be cached. Before doing the request to database you need to check whether such query results were stored before in the cache - and this search is done by the name of the cache file - which you have assigned before.

drawbacks I see and why it does not comply with my requirments:
- imagine you have blogs displayed in page using queries like:

Code:

select ... from blogs ... limit 2, 10

so for every such a query (every separate page) I need to create a separate cache file. Of course, the naming can be done automatically, but then I need to create a delete scenario - that will check all such cache files containing 'blog' word inside name. That means I would not be able to use your standard methods

Code:

$this->mp_cache->delete()

- Also it would require to invent a workaround for queries wich have several tables requests. That means I would need to invent query parsing technique as some queries are built dynamically.
- the extra processing with IF clause around every query will require too much extra code which is not good as almost all models are already written. I was looking for something more automatic than manual saving of cache files.

Anyway, thanks for your help.