CODEWITHSUNDEEP
mysqlquery-optimization
Russell C.
Russell C.

Reputation: 1659

Improving MySQL Update Query Efficiency

In our database tables we keep a number of counting columns to help reduce the number of simple lookup queries. For example, in our users table we have columns for the number of reviews written, photos uploaded, friends, followers, etc. To help make sure these stay in sync we have a script that runs periodically to check and update these counting columns.

The problem is that now that our database has grown significantly the queries we have been using are taking forever to run since they are totally inefficient. I would appreciate someone with more MySQL knowledge than myself to recommend how we can improve it's efficiency:

update users 
    set photos=(select count(*) 
                    from photos 
                    where photos.status="A" 
                        AND photos.user_id=users.id) 
    where users.status="A";

If this were a select statement I would just use a join but I'm not sure if that is possible with update.

Thanks in advance for your help!

Upvotes: 1

Views: 762

Answers (1)

Pentium10
Pentium10

Reputation: 207830

you can do something like this

UPDATE users u 
       JOIN (SELECT user_id, 
                    COUNT(*) AS c 
             FROM   photos 
             WHERE  status = "A" 
             GROUP  BY user_id) pc 
         ON pc.user_id = u.id 
SET    u.photos = pc.c

You need indexes on

  • (photos.status,photos.user_id)
  • (photos.user_id)
  • (users.id)

Upvotes: 4

Related Questions