telling code to pull from the database when the data in a table changes

[Nathan Nobbe]

All,

I recently began investigating memcached, and im very excited about it.  im
planning on using it w/ php to implement variables whose lifetime can be the
duration of the application.
I understand that storing the results of queries to the database can
increase efficiency if those variables are checked for the data first on
consecutive requests for the data they store, but
i still have a disconnect...
what i dont understand is when the data profile of a particular table
changes, and the variable in the memcache that represents that table becomes
stale, how can the variable be updated efficiently?
obviously w/ php and a  unix system, a simple cron could be used to poll the
database periodically to see if there are changes to the data profile of a
given table.  i was thinking to make this even more
efficient; triggers could be employed (im using mysql 5+) to update a table,
call it recent_tables_that_changed, dedicated to signifying particular
tables in the database have changed.  then the cron'd php script could
simply check recent_tables_that_changed  to determine which, if any,
application variables had become stale (do to a change in data profile).
the stale variable  could then be updated and the record removed from
recent_tables_that_changed.
though decent, i am personally not a fan of this approach, because there
would still be a frequent query running against the database just to
determine what data had changed so that application variables in the cache
would not remain stale for long once theyve become stale.  additionally, the
mechanisms' responsiveness would be limited to the time granularity of cron,
which i believe is a minute.  im wondering if there is some way to signal
the cache only when tables, represented by application variables, have
changed.  if you dont feel like typing out a response, because this is
probably a naive question, would anyone mind directing me to a resource or
two where i could read up on the topic myself?

(sorry for the lengthy posting)
thanks,

-Nathan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.danga.com/pipermail/memcached/attachments/20070521/ac143a94/attachment.htm