Are there better method to update a column in my database table rather than using MySQL Schedule eve-CodePudding

Frontend: Reactjs Backend: Nodejs Express Database: Mysql Workbench Hosting: Linode

Now I have an event that is scheduled to run every 2 seconds to update the status column. However, I am worried that the event might affect the performance of the server. Hence, I want to hear suggestions from you guys on what I should do.

I want to update the status column based on the start date & end date data in the database. The SQL code that I used for Mysql events is:

CREATE EVENT update_status
ON SCHEDULE EVERY 2 SECOND
DO
UPDATE `practiceme`.`events` as e
set `e`.`status` = 
CASE
WHEN CURRENT_DATE > `e`.`eventEnd` THEN 'Past'
WHEN CURRENT_DATE < `e`.`eventStart` THEN 'Upcoming'
WHEN CURRENT_DATE > `e`.`eventStart` AND CURRENT_DATE < `e`.`eventEnd` THEN 'Ongoing'
ELSE `status`
END

My database table "events" stores the start and end date.

CodePudding user response：

It's always a good idea to filter your records. What are the possible changes and when:

from Ongoing to Past (if it was Ongoing previously and ended)
from Upcoming to Ongoing (if it was Upcoming previously and started)

So:

CREATE EVENT update_status
ON SCHEDULE EVERY 2 SECOND
DO
UPDATE `practiceme`.`events` as e
set `e`.`status` = 
CASE
WHEN CURRENT_DATE > `e`.`eventEnd` THEN 'Past'
WHEN CURRENT_DATE < `e`.`eventStart` THEN 'Upcoming'
WHEN CURRENT_DATE > `e`.`eventStart` AND CURRENT_DATE < `e`.`eventEnd` THEN 'Ongoing'
ELSE `status`
END
WHERE 
((`e`.`status` <> 'Past') AND (`e`.`eventEnd` < CURRENT_DATE)) OR
((`e`.`status` <> 'Ongoing') AND (`e`.`eventStart` > CURRENT_DATE))

It is also a good idea to convert status into an enum and make sure that status defaults to Upcoming.

CodePudding user response：

Instead of bombarding the database with updates, you can query the status on demand. In fact, you don't even need to store the status:

SELECT *,
       CASE
           WHEN CURRENT_DATE > `e`.`eventEnd` THEN 'Past'
           WHEN CURRENT_DATE < `e`.`eventStart` THEN 'Upcoming'
           ELSE 'Ongoing'
      END AS `status`
FROM  `practiceme`.`events` e
-- Add a where caluse depending on what you need to query