How to find and store the number of times a user was part of a search criteria

Question

LinkedIn provides a mechanism to say that the user was part of a search criteria "n" number of times in the last "x" days. How do you go about capturing and storing the information. Are you supposed to iterate the search results and put an appropriate counter whenever they get searched or is there a non-intrusive mechanism by which this can be captured at a lower level (than the application layer)

Answer 1

The simplest approach would be to log all searches and then count (with SQL) how many times the particular user appears. Something to the effect of: SELECT COUNT(1) FROM search_log WHERE search_string = 'this-user-name'

This may be an impractical amount of data to query in real time, however, especially if you want to count searches that merely included the username (e.g., "puppies, kittens, and this-user-name"). In that case, the most direct approach is to simply include a field on your user table that counts searches. Then as you process each search you can look for any usernames in it and increment the appropriate counters.

Answer 2

you could do something like this (http://pastie.org/1211302):

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;

insert into users (username) values ('f00'),('bar'),('foobar'),('alpha'),('felix');

drop table if exists user_hits;
create table user_hits
(
hit_id int unsigned not null auto_increment primary key,
hit_date datetime not null,
user_id int unsigned not null,
key hit_date_user_idx(hit_date, user_id)
)
engine=innodb;

drop procedure if exists user_search;

delimiter #

create procedure user_search
(
in p_username varbinary(32)
)
begin

  set p_username = trim(replace(p_username, '%',''));

  create temporary table matches engine=memory 
    select * from users where username like concat(p_username,'%');

  insert into user_hits (hit_date, user_id) select now(), user_id from matches;

  select * from matches order by username;

  drop temporary table if exists matches;

end #

delimiter ;

drop procedure if exists user_hits;

delimiter #

create procedure user_hits
(
in p_user_id int unsigned,
in p_day_interval smallint unsigned
)
begin

 select 
  user_id, 
  hit_date, 
  count(*) as hits 
 from 
  user_hits
 where
  hit_date between now() - interval p_day_interval day and now() and 
  user_id = p_user_id
 group by
  hit_date, user_id;

end #

delimiter ;

-- testing

select * from users;
select * from user_hits;

call user_search('f00');

select * from user_hits;

call user_search('f');

select * from user_hits;

call user_hits(1,7);