CODEWITHSUNDEEP
mysqlprocedure
Manish
Manish

Reputation: 65

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:

user_logs table:

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?

Upvotes: 2

Views: 3648

Answers (2)

Rahul
Rahul

Reputation: 77866

Try using update join like

UPDATE users a 
    JOIN (
SELECT userid, COUNT(*) as count_login 
FROM user_logs 
GROUP BY userid) b ON b.userid = a.id 
SET a.logins = b.count_login;

Upvotes: 1

sagi
sagi

Reputation: 40481

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.

Upvotes: 1

Related Questions