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

    @Puristaako Автор вопроса
    Ответ полученный на англоязычном сервисе через 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
    Ответ написан