I don’t know if I have mentioned, but for me, when I am looking for a way to do something technical, I am most interested in looking at examples of other people’s code.  I am not so much into getting full explanations of how and why ( I can figure that part out on my own ), but I really just need to see some code.  So, here is some code for doing a fast update to a large mysql table.

I have a table to update that has over 22 million rows in it.  When I added a new int field to that table it took 4 hours to complete.  However, when I had to do an update to that table to set the values for some of the rows, I ran the following query and it only took like 30 or 40 minutes…

This is an interesting update, as it joins 2 big tables together, and then updates one of the fields from one of those tables with an aggregate that came from a third very big table.

update bigtable as t1 join anotherbigtable as t2 on (t1.common_key_id=t2.common_key_id)
   set t1.field_to_set = (
      select sum(t3.field_to_aggregate)
        from athirdbigtable t3
       where t3.another_common_key_id=t1.another_common_key_id
)
 where t2.valid_source_id in (6,4,1)
;

Obviously I was trimming out rows by using the valid_source_id check, but that is the point.  Doing an update like this can be pretty fast, if you aren’t updating each row.

Big win for me.