From: Date: April 1 2004 11:00pm
Subject: Re: How to speed UPDATES
List-Archive: http://lists.mysql.com/perl/2827
Message-Id: <406C9EF3.4738.1615B18@localhost>
MIME-Version: 1.0
Content-Type: Multipart/Alternative; boundary="Alt-Boundary-25055.23157528"

--Alt-Boundary-25055.23157528
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: Quoted-printable
Content-description: Mail message body

[snip]
> 
>         my $sth =3D $dbh->do( "
>                               UPDATE $table
>                               SET $SET
>                               WHERE ( epoch LIKE $corep  AND station =3D=
 $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 t=
rouble 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=F6nchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112



--Alt-Boundary-25055.23157528--