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#)
Understanding Notification and Polling said:
With polling, the database must be configured to include a table named AspNet_SqlCacheTablesForChangeNotification that has three columns - tableName, notificationCreated, and changeId. This table contains a row for each table that has data that might need to be used in a SQL cache dependency in the web application. The tableName column specifies the name of the table while notificationCreated indicates the date and time the row was added to the table. The changeId column is of type int and has an initial value of 0. Its value is incremented with each modification to the table.
In addition to the AspNet_SqlCacheTablesForChangeNotification table, the database also needs to include triggers on each of the tables that may appear in a SQL cache dependency. These triggers are executed whenever a row is inserted, updated, or deleted and increment the table s changeId value in AspNet_SqlCacheTablesForChangeNotification.
2. 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.