Alternatives to tracking queries of table rows than a "count" row

I need to keep track of the number of times each row in a table is queried. I need to do this over many tables.

One idea is the "search_count" column

id  | other data ..开发者_StackOverflow社区.  | search_count
 1  |      ...        |       12
 2  |      ...        |       90

Is there a more efficient way to do this than to have a "search count" column? I know this would work but its giving me a bad "code smell" because I would have to do an update after every query (Or a trigger?). We'll use Piwik or Google Analytics as much as possible but they wouldn't be completely accurate because we have more than just the web server interfacing the database.

That database is MySQL 5.x

Thanks

Doing this in the DB on the fly would generate far too many dead rows...

Track this using memcache->increment in your favorite language. It is atomic. And use a cron to store the results daily or hourly.

Pseudo-code to count:

$ns = $memcache->get('count_ns');
$memcache->increment("count-$ns-$table-$id");

On cron:

$ns = $memcache->get('count_ns');
sleep(1); // allow processes to finish
$memcache->increment('count_ns');
while ($row = $rows->next()) {
  if ($count = $memcache->get("count-$ns-$table-$id") {
    // increment count
  }
}