Improving MySQL Update Query Efficiency

Question

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!

Pentium10 · Accepted Answer · 2011-03-17 16:06:47Z

4

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)

answered Mar 17, 2011 at 16:06

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Russell C. Over a year ago

That did the trick. I didn't know you could use JOIN in that way. I guess you learn something new everyday. Thanks so much for your help!

Collectives™ on Stack Overflow

Improving MySQL Update Query Efficiency

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related