MySQL Trigger – Update Files or Table Every 5 Seconds or Less Frequent

jobsMySQLmysql-eventtrigger

There is a table 'votescount' which is being updated every time a user vote.

I need to display three rows with maximum votes.

Now the problem is If the users are less in number and less frequent to update, a trigger will be better to extract the rows, otherwise, if users are more frequently voting, an event will be better.

So, is there a lightweight solution in between to solve the problem?

I'll prefer to write the results in a file because it will be faster to access from php.

I am working on a LAMP stack.

Best Answer

This worked for me.

create event event_name on schedule every 5 second starts now()
do
begin
if (select update_time+5>=now() from information_schema.TABLES 
where table_name='Table_name')
then call procedure_name();
end if;
end//

Here the event checks every 5 seconds if any update happened in the last 5 seconds on the table. If yes it simply calls the procedure update_file or something.

Related Question