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);