CODEWITHSUNDEEP
pythonmysqlsqlcountmysql-python
David Bailey
David Bailey

Reputation: 623

How can one optimize this MySQL count algorithm?

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))

Upvotes: 1

Views: 194

Answers (2)

BellevueBob
BellevueBob

Reputation: 9618

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 5

Related Questions