Database change detection

Question

I need a way to detect changes of my database because I'm implementing a caching system on a client's site. (caching the whole page, not just the queries)

At the top of the every page (i have a "master page" so just one place in code) I query the database to see if its size is changed like so.

I sum the size and get a "unique" number. Compare it to the previous one, and if the values are the same, deliver the cached page, if not, run the "normal" page and then cache that.

So I think in theory this could work, because I have just 1 database query, and based on that deliver the site (cached or uncached depending on the result) to the user.

Could this work, and if not - why?

EDIT:

what about cheking for the last update timestamp? LINK

Answer 1

Who is updating the database? Suggest that the process causing the change should be the one invalidating your cache. When a user/admin causes a change to occur to the table, you could:

• if the admin and the public-facing site share the same application space (i.e. they ARE the same app), then simply update the cache at the time the admin makes a change.
• set a status bit somewhere (session variable perhaps), and periodically check to reload your cached data.

Suggest size is not a good indicator of 'change occurred'. Forget rows - what if a bool/bit value changes? That'd satisfy the condition to invalidate the cache, but the 'size' would always be the same. You could improve this with a last-change-datetime, but you're still having the same basic problem - querying the database to check for a change flag. Kind of defeats the purpose of a cache.

Consider another approach - reload your cache every n minutes. Whether there's been a change or not, just reload it. n will be a value that fits in your business requirements. You could have stale cache, and be serving old/stale data for at most n minutes. This wouldn't be my suggestions, but something to consider.

Answer 2

Interesting approach.

According to http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html, you may get approximate values for the Data_length values - and in the example link, there were suspiciously round numbers. I'd definitely experiment with that some more to make sure it does what you think it does.

You should also make sure this works for updates, not just deletes/inserts.

I'd also test the performance of the "has anything changed" query on a non-trivial system. I'm guessing that for this to be accurate, the DB server would need to look at the file system, which I'm guessing is many times slower than querying it through SQL.

Secondly - depending on the underlying application - you may well find your application data changes so often that you are constantly invalidating the cache.

Answer 3

You can use this :

<?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 />
';
}

?> 

Answer 4

You can CREATE TRIGGERs AFTER UPDATE, INSERT, DELETE and REPLACE ON the table you want to monitor that insert CURRENT_TIMESTAMP() into some kind of a status field in a separate table. At the top of each page read this value and compare it with your cache's timestamp, if it is older than your cache, use the cache.

Answer 5

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

tryed this, and it works like a charm....