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.
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.
source to share
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.
source to share