Question 21510 in Mysql Question posted in
The official MySQL documentation can be found here.

Are there better method to update a column in my database table rather than using MySQL Schedule events?

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.

Database table

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search