Re: How to speed UPDATES

[Etienne Orliac]

Thanks for the solution. I put a key on the two columns and now the process is incredibly
much faster.

Thanks a lot,

Etienne.

__________________________________________________

Etienne J. Orliac
Ph.D student
Institute of Engineering Surveying and Space Geodesy (IESSG)       
School of Civil Engineering
University of Nottingham
University Park
Nottingham NG7 2RD, UK

e-mail: isxejo@stripped 

Tel   : [phone] & ext: 18508
Mob : [phone]         
Fax  : [phone]     
>>> "Ulrich Borchers" <borchers@stripped> 04/01/04 10:00 PM
> >>>
[snip]
>
>         my $sth = $dbh->do( "
>                               UPDATE $table
>                               SET $SET
>                               WHERE ( epoch LIKE $corep  AND station = $corsite)
>                               LIMIT 1
>                              ");
> }
>
>
>
>
> +-----------------------+---------------+------+-----+---------+-------+
> | Field                 | Type          | Null | Key | Default | Extra |
> +-----------------------+---------------+------+-----+---------+-------+
> | epoch                 | varchar(12)   | YES  |     | NULL    |       |
> | station               | varchar(4)    | YES  |     | NULL    |       |
> | ztd_nrt               | decimal(6,1)  | YES  |     | NULL    |       |
> | std_ztd_nrt           | decimal(6,1)  | YES  |     | NULL    |       |
> | flag_ztd_nrt          | varchar(50)   | YES  |     | NULL    |       |
> | iwv_nrt               | decimal(6,1)  | YES  |     | NULL    |       |

You don't have any keys ----------------------------^

Try
 create index idx_epoch on table (epoch)
 create index idx_station on table (station)

or
 create a primary key on these two columns (must not be null)

Avoid keys on other columns. Creating the indexes will very likely take a while :-)
If you can change the two columns to INT, maybe as foreign keys, do so.
That might gain even more speed. But the missing keys cause you the most trouble I
think.
I don't know, if a very long $SET string will also slow things down...

Uli

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. [phone]
Mobil (0 179) 72 66 112