Re: Why Does UPDATE Take So Long?

Many thanks to everyone who helped me with this. It'll be a while before
I understand enough to be able to do a good job of tuning my system's
configuration, but there seem to be a few basics I can apply right away.
Also pointing out how UPDATE actually works was very helpful. Since I'm
at the data building stage, most of my updates will apply to an entire
column and in cases like that it's much more efficient to simply use
joins into a new table and delete the old. In this case:

CREATE TABLE farm2 (LIKE farms);
INSERT INTO farm2 (farm_id, fips_cd, farm_nbr, prog_year) SELECT
farm_id, fips_cd, farm_nbr, '2007' FROM farms;
DROP TABLE farms;
ALTER TABLE farm2 RENAME TO farms;
CREATE UNIQUE INDEX farms_id_key ON farms(farm_id);
CREATE UNIQUE INDEX farms_fips_nbr_key ON farms(fips_cd,farm_nbr);

takes only a few minutes for this 2.77 million record table. The alternative

UPDATE farms SET prog_year='2007';

takes hours! I don't know how many because I gave up after waiting for
1.5 hrs.

Thanks all,
- Bill Thoen