Basically, I have a table called event:

id | title | countdown | description | active | expired | created\_at | updated\_at

I was starting to concept how to create "LIVE" events using MySQL or PHP. So I was thinking I could use either a Cron referencing PHP or an event using straight SQL.

So my ideas were the following:

Restructure my tables to:

events:
id | title | description | created\_at | updated\_at

event\_queue:
id | event\_id

active\_events:
event\_id | countdown | iteration

expired\_events:
event\_id | expired\_at

So having that, how can I create a live timer to run every second to update, at most, 100 rows in active\_events every second?

Example data to go inside active\_events is as follows:

event\_id: 1,
countdown: 20 (for 20 minutes),
iteration: 90

I want to decrement countdown every second and when it hits 0, to decrement the iteration and start the countdown back to 20. Once the iteration is at 0 and countdown is at 0, just remove the entry completely, then archive the event to expired\_events. Again, only a max of 100 rows will be inside active\_events.

What's the best way to go about doing this?

Basically this is an internal application that people can vote on in the office and if no votes are done, then it goes away. The above structures are how I want it and see it working.

Any thoughts? (I have to go to lunch and will update this or leave comments on any posts later)

EDIT:
Back from lunch!... I forgot to note that the countdown needs to reset every time an action is placed on it (a user votes on it). For the sake of my brain and thinking, let's say every time someone accepts one of the events, it is stored into accepted\_events, and the active\_events countdown has to be reset to 20.

Here is my server's top running the MySQL event and not:

RUNNING IN VM INSTANCE ON MACBOOK PRO
=====================================

This is without the event
=========================

top - 11:56:59 up 1:29, 1 user, load average: 0.11, 0.06, 0.01
Tasks: 92 total, 2 running, 88 sleeping, 2 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 510552k total, 499364k used, 11188k free, 74316k buffers
Swap: 1048568k total, 0k used, 1048568k free, 251872k cache

This is with the event scheduled and running the following SQL on 200 rows:
===========================================================================

update test set timer = timer-1
===============================

top - 12:00:08 up 1:32, 1 user, load average: 0.06, 0.04, 0.00
Tasks: 92 total, 2 running, 88 sleeping, 2 stopped, 0 zombie
Cpu(s): 0.3%us, 0.3%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.3%hi, 0.0%si, 0.0%st
Mem: 510552k total, 501720k used, 8832k free, 74996k buffers
Swap: 1048568k total, 0k used, 1048568k free, 253244k cached

EDIT # 2: Here's a sketch of what I mean -- <http://oi51.tinypic.com/29c1bp4.jpg>