I want a method to identify changes of my database because I am applying a caching system on the client's site. (caching the entire page, not only the queries)

Towards the top of the every page (i've got a "master page" so only one devote code) I query the database to ascertain if its size is changed like so.

I sum the dimensions and obtain a "unique" number. Compare it towards the previous one, and when the values are identical, provide the cached page, otherwise, run the "normal" page after which cache that.

And So I think theoretically this might work, because I've just 1 database query, and according to that provide the site (cached or uncached with respect to the result) towards the user.

Could the work, and when not - why?

EDIT:

how about cheking during the last update timestamp? LINK

Who's upgrading the database? Claim that the process leading to the modification ought to be the one invalidating your cache. Whenever a user/admin leads to a switch to happen to the table, you can:

  • when the admin and also the public-facing site share exactly the same application space (i.e. those are the same application), then simply just update the cache at that time the admin constitutes a change.
  • set a standing bit somewhere (session variable possibly), and periodically check to reload your cached data.

Suggest size isn't a good indicator of 'change occurred'. Forget rows - let's say a bool/bit value changes? That'd fulfill the condition to invalidate the cache, however the 'size' would continually be exactly the same. You can improve this having a last-change-datetime, but you are still getting exactly the same fundamental problem - querying the database to check on for something new flag. Type of defeats the objective of a cache.

Consider another approach - reload your cache every n minutes. Whether there is a big change or otherwise, just reload it. n is a value that matches inside your business needs. You might have stale cache, and become serving old/stale data for for the most part n minutes. This would not be my suggestions, but something to think about.

Interesting approach.

Based on http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html, you might get approximate values for that Data_length values - as well as in the example link, there have been suspiciously round amounts. I'd certainly test out that more to make certain it will what you believe it will.

It's also wise to make certain this works best for updates, not only removes/card inserts.

I'd also test the performance from the "has anything transformed" query on the non-trivial system. I am speculating that with this to become accurate, the DB server will have to consider the file system, which I am speculating is many occasions reduced than querying it through SQL.

Next - with respect to the underlying application - you might well find the application data changes so frequently that you're constantly invalidating the cache.

This can be used :

<?php

mysql_connect("localhost","root","");
$result = mysql_query("SHOW TABLE STATUS FROM test;");
while($array = mysql_fetch_array($result)) {
$total = $array[Data_length]+$array[Index_length];
echo '
Table: '.$array[Name].'<br />
Data Size: '.$array[Data_length].'<br />
Index Size: '.$array[Index_length].'<br />
Total Size: '.$total.'<br />
Total Rows: '.$array[Rows].'<br />
Average Size Per Row: '.$array[Avg_row_length].'<br /><br />
';
}

?> 

You are able to CREATE TRIGGERs AFTER UPDATE, Place, Remove and REPLACE Up for grabs you need to monitor that place CURRENT_TIMESTAMP() into some type of a standing area inside a separate table. Towards the top of each page look at this value and compare it together with your cache's timestamp, if it's over the age of your cache, make use of the cache.

SELECT max(UPDATE_TIME)
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'DB_NAME_GOES_HERE'

tryed this, and delay pills work like no bodies business....