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

[Tomas Markauskas]

Hello,

why not deleting/updating the cache entry when you updating the table?
At that moment you know, which rows you're updating.

Tomas

On 5/21/07, Nathan Nobbe <quickshiftin at gmail.com> wrote:
> 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
>