Cdbcachedependency Effective Sql That Handles Modifications And Deletions?

Looking for efficient sql to use for an CDbCacheDependency that can handle new records, modified records, or deleted records. I’ve been using sql such as:


SELECT MAX(modified) FROM post

However, I realized that although this handles creations and modifications, it doesn’t handle deleted records.

Came across this forum post, CDbCacheDependency subsequvent calls, which discusses using AVG instead of max which sounds pretty decent (almost effectively creating a hash of the date values):


SELECT AVG(UNIX_TIMESTAMP(last_updated)) FROM post

However, wondering if this is the best method or are there alternatives?

in mysql you can get table last modyfication timestamp:




SELECT UPDATE_TIME

FROM   information_schema.tables

WHERE  TABLE_SCHEMA = 'dbname'

   AND TABLE_NAME = 'tabname'



it will cover any changes, including deletions. I do not know about such mechanism in other dbms…

Thanks that looks promising except I’m reading this only works for MyISAM tables and not InnoDB (which I am using).

could be… I was using it only for MyISAM tables

Hi Codebutterfly and redguy,

One thing I’m not very sure is the performance hit of AVG function.

I guess MAX might not be very heavy when you have an index on the column, but what about for AVG? I’m afraid that DBMS has to read all the records to calculate the average value.

That’s a good point. For my particular use case there will probably only be in the hundreds of records max so probably not that big a deal. Do you have any suggestions for an alternative? Two other thoughts I’ve had is:

  1. make a special cache dependency table where values are updated whenever relevant data changes. Similar to ASP.NET’s SqlCacheDependency implementation. See Using SQL Cache Dependencies (C#):
  1. use something like:

SELECT CONCAT(CAST(MAX(modified) AS CHAR), COUNT(*)) FROM post

If a record is added the max modified will change (as well as the count), if a record is deleted the count will change, if a record is added and deleted at nearly the same time the count will stay the same but the max modified will change. Not sure how the performance of this would compare to using AVG.

Wow, nice. :D

the second is quite nice, but I would go with the first one: create table last_table_modyfications (table_name varchar primary key, last_modyfication datetime) and put in that table record for every table that should be tracked, like (‘post’, now()) and then update that table with db triggers on after insert,update,delete. this will be fast (it is simple hash table), relayable and as most db engines have at least sql triggers - should work everywhere.

In mysql you could create such table as HEAP table so it will be stored in memory (but you should then handle missing table record in triggers because on server restart all data will be gone)!

@redguy

Sounds quite a reasonable solution. :) Thanks.

It could be:




-- PostgreSQL

SELECT

    	 CAST(MAX(id) AS TEXT) || CAST(COUNT(*) AS TEXT) || CAST(MAX(created_on) AS TEXT) || CAST(MAX(modified_on) AS TEXT) AS "dependency"

FROM Post;


-- MySQL

SELECT

    	CONCAT(MAX(id), COUNT(*), MAX(created_on), MAX(modified_on)) AS `dependency`

FROM Post;