Re: How to speed UPDATES

[Ulrich Borchers]

[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