P
P
Puristaako2014-12-22 07:18:04
PHP
Puristaako, 2014-12-22 07:18:04

What is the best way to store a timestamp in the database for each action?

Let me simplify a bit to make it clearer.
There are 2 buttons A and
B. It is necessary to store (in the database) the time the buttons were pressed (up to seconds) and which button was pressed, how best to do this?
If you store everything in epoch in one line (example: "AD549795d1BD549795d3BD549795d6"), then the line can potentially grow up to 15Kb (even if it's time to translate in hex), it will be requested often, 100-300 (at the peak) users, every time in 10 seconds (540-1620 MB/hour).
Store the time of the first click in another row in the database, and then add the difference to the row?
Store in a separate table separately with TIMESTAMP for each click?
Of course the goal: less load on the server, less traffic
ADDED:
Sorry for the inaccurate question.
There are many operators (who press buttons). Here is the most suitable analogy: A
football match and an operator who keeps statistics (who gave a pass to whom, shots on target (buttons-events), etc.) There are many operators, each operator has many matches, n to n. One match has only one operator. 1 to 1. You need to store the match, the event and the time of this event in the match.
ps When you do this all the time, everything is logical and simple, but when, 7 years after the university, you try to remember something in your head, it's a mess. Especially when, having asked a question, people, before giving an answer, first elevate themselves / belittle the interlocutor, and then answer the question. There are doubts about the professionalism of these comrades and you always treat their answers with suspicion. I have never met professionals in my life who would answer questions in such a vein. Hope I didn't offend anyone.

Answer the question

In order to leave comments, you need to log in

6 answer(s)
P
Puristaako, 2014-12-22
@Puristaako

The answer received on the English service after 10 minutes:
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 should probably 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

F
FanatPHP, 2014-12-22
@FanatPHP

It is necessary to store (in the database) the time of pressing the buttons (up to seconds) and which button was pressed, how best to do this?

The table
is the ONLY option that should automatically come to mind.
All other fantasies must be beaten out of you with a red-hot iron.
The sleep of the mind gives birth to monsters, and the fantasy of a scamp, not burdened even by the basic principles of programming, gives birth to monsters.
Where a person even with the level of a specialized college (former vocational school) does not think for a minute about the problem of the "how much will be 2x2" level, the valiant warrior while mysql_fetch_array begins to wrinkle his forehead, bend his fingers, invent a universal problem for himself. And then a monster to solve it.
In general, I would legally forbid taking care of servers in the code, at least during the first three years of coding. never before has this concern brought anything useful, but all sorts of rubbish - in innumerable quantities.
He has never seen a server, but he diligently saves the servers of the whole world from excessive load. And of course, as a result, it receives an increased load . Since solving non-existent problems, out of illiteracy, he begins to invent ways of storing data that are ten times worse than standard ones.
We go to the manual and see how much each type takes:
  • int - 4 bytes
  • timestamp (mysql) - 4 bytes
  • datetime - 8 bytes - more than int, but still less than homemade hex encoding.

N
neochar, 2014-12-22
@neochar

Use google analytics and its API.

L
LLWE_FUL, 2014-12-22
@LLWE_FUL

Store a string with a time stamp for each click, send requests to the server immediately upon clicking, take the time from the server in the database (default CURRENT_TIMESTAMP), and not send it from a client that does not know how the clock is set.

X
xmoonlight, 2014-12-22
@xmoonlight

Depends on the task.
You can store: start+offset1+offset2+...+offsetX
You can store: count, average time and last click time.
Can be stored: in cookies on the user's side and transferred in batches.
there are more options...
Describe the problem in more detail..

E
Eugene, 2014-12-22
@Nc_Soft

If the load on the database soars, then use redis.
But as mentioned above, you need to store it in the format user / button / timestamp (int)

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question