individual or master ??

[midnite]

i would like to show the time of a user last login upon that particular user login again. For example, if i logged in at 2/4 6:30. And when i login again next time, the system will remind me that "Last login: 2nd April, 2007 6:30p.m."i will keep a log for all the login and logout. So you can imagine that it will be a huge table!!And also, i will have a table holding members' information of course.Here comes my question,1) should i add a field "last_login" in the members' table,2) or querying the most recent date for a particular member's ID in the login logout log table for the last login information?Method 2 is better if this query is fast enough and mySQL allows many queries to access the same table at the same time.Otherwise, method 1 will do a lot better in performance but it has data redundancy.

[Yahweh]

i would like to show the time of a user last login upon that particular user login again. For example, if i logged in at 2/4 6:30. And when i login again next time, the system will remind me that "Last login: 2nd April, 2007 6:30p.m."i will keep a log for all the login and logout. So you can imagine that it will be a huge table!!And also, i will have a table holding members' information of course.Here comes my question,1) should i add a field "last_login" in the members' table,2) or querying the most recent date for a particular member's ID in the login logout log table for the last login information?Method 2 is better if this query is fast enough and mySQL allows many queries to access the same table at the same time.Otherwise, method 1 will do a lot better in performance but it has data redundancy.

I recommend method 1, even if it means having redundant data, because if you wanted to display a user and the last login time in the same query, you'd need a really awkward join:

Select	u.user_id,	u.user_name,	Max(l.last_login)FROM	User_table uLEFT JOIN	Login_table l		ON			(u.user_id = l.user_id)GROUP BY	u.user_id,	u.user_name

Aggregate functions and joins on huge tables are murder on a database, and your site will begin to slow down over time to the point where it no longer wants to function at all.I have a website that works somewhat like a blog: articles are posted, and people can make comments to the article. Articles are stored in their own table, comments are stored in another. I wanted to include a link to the most recent comment, so I had to use a join somewhat like the one above, but after 20 000+ articles, and 100 000+ comments, queries kept timing out. I eventually added a "last_comment" field on my table, and it *dramatically* increased the speed of my queries.If you just add another field, you gain a lot of performance:

SELECT	user_id,	user_name,	user_last_loginFROM	user_table;

[midnite]

Thanks for your help and sharing your experience =)What if adding an index to the field last_login in the table Login_table? Doing such a MAX() and JOIN will be just working on indexes. Will it be better?i am not feeling good if i am keeping any unnecessary redundant data. And also it has the risk of data inconsistency (login time is not that important though, if in the case of a forum or blog likes yours, it will be strange and unacceptable for the inconsistency). Of course, if you have already get your blog's joining and sorting fields indexed and it also cause a dramatic decrease in performance, i must buy your point =)