I have two tables user & user login history. I need to make a report of the times a particular user is logging into the system. The table contains millions of rows of data. So running a nested query to fetch number of logins of users is taking a lot of time.
I am trying to loop through all the users and update the logins column. How can I do this in one query?
The schema is like this:
UPDATE users u
SET u.logins = (SELECT COUNT(*)
WHERE userid = u.id)
An update should take so long, especially if you have proper indexed on both tables.
UPDATE users u INNER JOIN(SELECT ul.userid,count(1) as cnt FROM user_logs ul GROUP BY ul.userid) u2 ON(u2.userid = u.id) SET u.logins = u2.cnt
Then make sure you have the following indexes:
users - (id,logins) user_logins - (userid)
If that doesn't help - try doing this in two steps , build a derived table with the sub query results, and update by it :
CREATE TABLE temp_for_update AS( SELECT ul.userid,count(1) as cnt FROM user_logs ul GROUP BY ul.userid); CREATE INDEX YourIndex ON temp_for_update (userid,cnt); UPDATE users u INNER JOIN temp_for_update u2 ON(u2.userid = u.id) SET u.logins = u2.cnt
This should defiantly be faster.