Best way to store large amount of timestamped data in MySQL
what should I do?
Imagine a tennis match.
Buttons for pressing buttons (actions) "Ace", "Fault", "Winner", "Uncedced error", etc. We have many operators, matches at the same time. And we have many db requests from users (~ 1000 per minute).
What's the best way to store match_id, player, action, time_of_action?
1) a table with 1 row for each match: match_id, actions. Actions, players, timestamp encoded in 1 line#of player TINYINT id of action CHAR timestamp TIMESTAMP
example: actions = "1A2014-11-28 09:01:21 2W2014-11-28 09:01:33 1F2014-11-28 09:01:49"
2) a table with several rows for one match: id, match_id, player, action_id, the current timestamp (id PRIMARY KEY) it will be about 250 thousand rows in one day (300 per match * 40 matches in 1 tournament * 20 tournaments in day)
which is better: many rows and many queries SELECT player, action_id, timestamp FROM score WHERE match_id = N or the same number of queries, fewer rows (/ 300), but much more data in rows?
sry for my ugly tongue, I hope you understand me, if not, tell me
add: I'm going to use it for live or post-match match statistics. Open user page Federer - Nadal compliance statistics and every 10-30 seconds page refresh Example: http://www.wimbledon.com/en_GB/slamtracker/slamtracker.html?ts=1419259452680&ref=www.wimbledon.com/en_GB/slamtracker/ index.html & syn = none &
source to share
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 propose to create an event table containing the following items in the following order.
match_id
ts (TIMESTAMP)
action_id
player_id
You must include all four of these columns in the composite primary key, in the order in which I showed them.
Every time your counters record an action, you are inserting a new row into this table.
If you want to display actions for a specific 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 = <<whatever match ID>>
ORDER BY event.match_id, event.ts
Because of the order of the columns in a composite primary key in a table, event
such a query will be very efficient even if you insert many new rows into that table.
MySQL is built for this type of application. However, when your site starts receiving tons of user traffic, you should probably arrange to run those queries only once every few seconds, cache the results, and use the cached results to send information to your users.
If you want to get the match IDs for all the matches that are currently active (i.e. with an event in the last ten minutes), you can do that.
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 make a query like this a lot, I suggest you create an additional index on (ts, match_id)
.
source to share