Search code examples
phpmysqlperformancemysqlicyclic

Update database table cyclically every 5 minutes

I have to populate and update one of my MySql database table using a complex and expensive query, based on selection from other table's data. This table doesn't need to be always fully updated when i make a query on it, but i'd like to have a cyclic update every 5 minutes.

This automatic update should be infinite and i need to be sure that it never stops.

After some research, i've found some solution, but i don't know which is better for security and performance.

One of these could be my goal:

  1. Don't create table and make complex query from php every time to get the desired result
  2. Create a php script that repeats cyclically and update table db, maybe using Cron Job.
  3. Update table using a sql event

I think that first solution could be to expensive since query is complex and there could be many request every second, but the result is always updated. I don't have experience about Cron Job, so i can't know if it could be a good idea or not. For the third solution, i still don't have database privileges to run events, but i'd like to know if it could be a valid solution.

All other solutions are welcome, thanks.

Solution

  • Do not use cron. Think about what will happen if one instance goes beyond 5 minutes and the next starts up. Eventually you will have hundreds of copies bogged down stumbling over each other.

    Instead have a single job in a loop doing the update. (OK, you could have a cron job to perform a "keep-alive" task of restarting the query if it dies.)

    The job would

    CREATE TABLE new ...
INSERT INTO new  SELECT complex-stuff...
RENAME TABLE real TO old, new TO real;
DROP TABLE old;
loop.