Joining another table or not?

Question

Basically I have 2 tables. users and users_activity . I use a mysql table and the number of users is almost 5000. Each user has many actions, about 50-150 operations. When I want to get the user list table (ten by ten), I need to display the date of the last user activity. In this case, I have two options:

First option:

I add the last_activity column to users and select like this:

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

If I want to add a new action:

 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 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 selection, I can get the latest users_activity FROM users_activity and use it in sql update.

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

The second option:

I last_activity column from users and 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 action:

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

if I want to cancel last_activity :

 DELETE FROM users_activity WHERE activity_id = 100 LIMIT 1 

I am using mysql. Both tables: innoDB .

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

Answer 1

First of all, I want to say that the syntax of the insert statement is incorrect. Because the insert statement does not work with the where clause. If I understand you correctly, you want to show user information and the date of the last action. If your tables are well indexed, you can make the following 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 

Answer 2

You want to use the second script, two tables. Thus, you don’t have to worry about how to manage the last activity date, as well as the activity list, and you can always get the latest activity date by choosing max (users_activity_date) for the user with left connection and grouping for each user.

As a disclaimer, you can try both and see which is more effective in your situation. Scenario 1 provides performance benefits for your reporting request, but Scenario 2 will provide activity tracking performance updates. With any performance issues, no best practice or recommendation can truly replace testing in your environment with a realistic workload.