How can one optimize this MySQL count algorithm?

Question

I have 2 tables; one is users and the other records user actions. I want to count the number of actions per user and record this in the users table. There are ~100k user and the following code takes 6 hours! There must be a better way!

def calculate_invites():

sql_db.execute("SELECT id, uid FROM users")

for row in sql_db:
    id = row['id']
    uid = row['uid']

    sql1 = "SELECT COUNT(1) FROM actions WHERE uid = %s"
    sql_db.execute(sql1, uid)
    count_actions = sql_db.fetchone()["COUNT(1)"]

    sql = "UPDATE users SET count_actions=%s WHERE uid=%s"
    sql_db.execute(sql, (count_actions, uid))

Answer 1

You can do this all as one statement:

update users
    set count_actons = (select count(*) from actions a where a.uid = users.uid)

No for loop. No multiple queries. Do in SQL what you can do in SQL. Generally looping over rows is something you want to do in the database rather than in the application.

Answer 2

Offered only as an alternative since Gordon's answer is probably faster:

update users
from (
   select uid, count(*) as num_actions
   from actions
   group by uid
   ) x
set count_actions = x.num_actions
where users.uid=x.uid