Как оптимальнее хранить в БД timestamp для каждого действия?

Ответ полученный на англоязычном сервисе через 10 минут:

I suggest you create reference tables called

match match_id, name, venue A row for each distinct match
player player_id, name A row for each distinct player
action action_id, name This is a codelist 1=Ace 2=Fault, etc.
These tables will be relatively static.

Then, I suggest you create an event table containing the following items in the following order.

match_id
ts (TIMESTAMP)
action_id
player_id
You should include all four of these columns in a composite primary key, in the order I have shown them.

Every time your scorers record an action you'll insert a new row to this table.

When you want to display the actions for a particular match, you can do this:

SELECT event.ts,
action.name AS action,
player.name AS player
FROM event
JOIN player ON event.player_id = player.player_id
JOIN action ON event.action_id = action.action_id
WHERE event.match_id = <>
ORDER BY event.match_id, event.ts
Because of the order of columns in the composite primary key on the event table, this kind of query will be very efficient even when you're inserting lots of new rows to that table.

MySQL is made for this kind of application. Still, when your site begins to receive tons of user traffic, you probably should arrange to run these queries just once every few seconds, cache the results, and use the cached results to send information to your users.

If you want to retrieve the match IDs for all the matches presently active (that is, with an event within the last ten minutes) you can do this.

SELECT DISTINCT match.id, match.name, match.venue
FROM event
JOIN match on event.match_id = match.match_id
WHERE event.ts >= NOW() - INTERVAL 10 MINUTE
If you need to do this sort of query a lot, I suggest you create an extra index on (ts, match_id).

Ollie Jones