1

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.

2
  • 1
    Is there a timestamp associated with each vote? if so, index the timestamp column, and check if when the last vote was entered. If less than five minutes ago, have the event create the file. if more than five minutes ago, have the trigger create the file.,
    – RDFozz
    Mar 25 '17 at 17:40
  • A MySQL Trigger or Event or Stored Routine cannot write a file.
    – Rick James
    Mar 30 '17 at 1:07
1

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.

1
  • Caution: information.TABLES.update_time exists only for MyISAM tables (or maybe in some versions), so this technique cannot work without using that deprecated Engine for your important tables.
    – Rick James
    Mar 30 '17 at 1:10
0

This question may be "premature optimization". Simply have the PHP code fetch the desired data when it needs to show it. That way it will always be up to date, not even 5 seconds late.

If you think this will not work, please provide SHOW CREATE TABLE and the associated queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.