Select & update in same query

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:

users table:

id INT(10)
username VARCHAR(7)
logins INT(10)

user_logs table:

id INT(10)
userid INT(10)
login_date DATETIME(19)

http://sqlfiddle.com/#!9/dc4149

I'm running this query

UPDATE users u
SET u.logins = (SELECT COUNT(*) 
                FROM user_logs 
                WHERE userid = u.id) 
LIMIT 1

This is not working.

Is there any way how I could loop through users & update their respective login count?

I tried doing this with PHP but as the tables are very large. Doing this 1 by 1 takes very time.

Can I do this via command line?

Solution

An update should take so long, especially if you have proper indexed on both tables.

Try this:

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.