Improving MySQL Update Query Efficiency
- by Russell C.
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!
