Joining to another table or not?

Question

Basically I have 2 tables. users and users_activity. I use mysql table and the count of users almost 5000. Each users have many activities, about 50-150 activities. When I want to fetch the list of users table (ten by ten), I have to display user's last activity date. In this case I have two options:

First Option:

I Add column last_activity to users table and do select like this:

SELECT * FROM users ORDER BY id DESC, lIMIT 0, 10

If I want to add a new activity:

INSERT INTO users_activity (userId, date) VALUES(19, "2016-04-06")

UPDATE users SET last_activity = "2016-04-06" WHERE id = 19 LIMIT 1

If I want to cancel the last_activity:

DELETE FROM users_activity WHERE activity_id = 100 LIMIT 1

SELECT date FROM users_activity WHERE userId = 19 ORDER BY DESC LIMIT 1

Using this select, I am able to fetch the last date FROM users_activity table and use it in update sql.

UPDATE users SET last_activity = "2016-04-02" WHERE id = 19 LIMIT 1

Second option:

I remove column last_activity from users table and do select like this:

SELECT
  users.*,
  users_activity.date
FROM
   users
LEFT JOIN
   users_activity ON users_activity.userId = users.id
GROUP BY users.id
ORDER BY
   users.id DESC, users_activity.date DESC
LIMIT 0, 10

If I want to add a new activity:

INSERT INTO users_activity (userId, date) VALUES(19, "2016-04-06")

if I want to cancel the last_activity:

DELETE FROM users_activity WHERE activity_id = 100 LIMIT 1

I use Mysql. Both tables are innoDB.

In this situation, which way would you recommend me and why?

Answer 1

First of all I want to say your insert statement syntax is wrong. Because insert statement does not work with where clause. If I understant you correctly you want to show user's informations and it's last action date. If your tables well indexed that you can do it the next query:

SELECT u.*, a.action_date FROM users u
LEFT JOIN users_activity a ON a.userId = u.userId
GROUP BY a.userId
ORDER BY a.action_date desc
LIMIT 0, 10