Clever Geek Handbook

MySQL + PHP is the best way to store the timestamp every time a user logs in

I need to keep track of all user logins so that every time they register, I have a timestamp of when they do it. What is the most efficient and best way to do this?

I've always done this just by having a text field in the users table that stores unix timestamps separated by a character. Each time you log into the system, another timestamp is added.

1243453458|1255476933|1263453743|1318495068|

Is this a bad way to do it? I figured out a separate table of logins with a row each time each user logs in is a complete redundancy and will result in much more stress in the DB when retrieving this information.

Remember that I am handling these timestamps in PHP and also generating graphs using jQuery from data.

+3


source to share


3 answers


Yes, this is a terrible way to store anything, including timestamps, and there are much better ways to do it. Even the simplest table will give you much better performance:

user_id (int) | last_login (timestamp)


  • you store the timestamps as a string. It is completely inefficient for storage, retrieval, and updating; plus you will end up with a column limit eventually.
  • There are highly optimized data types for this kind of purpose - MySQL server developers tend to know something about storing data.
  • the database hit for such a tiny fixed-sized table would be negligible - unlike a variable-sized row table, which requires further processing in the application.

In other words, trying to optimize the application (based on the erroneous assumptions - "another table is too heavy load"), you pessimized it, coming up with a universal inefficient solution. Don't overload things; and if in doubt, go through both and see for yourself.

+5


source


You are probably better off putting it on a separate table in your database, for example:

members_logins
member_id (int) | timestamp (timestamp)


This would mean that it would be easier to perform operations.

+4


source


In mysql, you can use TIMESTAMP

Look here on how to set it up

EDIT: Your channel-limited approach is not appropriate. You can have a transaction table that stores all kinds of custom events.

0


source






More articles:


All Articles