
Danni
Novice
Apr 25, 2002, 5:53 PM
Post #5 of 5
(12798 views)
|
Theres a few things you can do... first if your doing a lot of lookups on the table via the email field you could concider making it a FULLTEXT key or index, this can make look ups a lot quicker at the cost of some disk space. Second, if the email field is unique you can put a limiter on the query so it doesnt have to continue searching after it sets the status. Heres an example...
foreach $email (@file) { chomp $email; my $qy = "UPDATE PDmail SET status = ? WHERE email = ? LIMIT 1"; $sth = $dbh->prepare($qy); $sth->execute("active",$email); $sth->finish; } You could possibly also include a status != "active" to ignore rows already activated, but that may slow it more also. BTW mysql shouldn't lockup for long with only 2566 records, could be a buggy installation. I have a very large table around 1.9Mil records with no problems, a little conversation with it is below...
mysql> select count(*) from ref; +----------+ | count(*) | +----------+ | 1953205 | +----------+ 1 row in set (0.00 sec) mysql> select * from ref where name = "LEISTON" LIMIT 1; +---------+----+-------------+------------+ | name | cc | lat | lon | +---------+----+-------------+------------+ | LEISTON | UK | 52.20000000 | 1.56666670 | +---------+----+-------------+------------+ 1 row in set (0.04 sec) mysql> update ref set cc = "XX" where cc = "CA"; Query OK, 22232 rows affected (1.15 sec) Rows matched: 22232 Changed: 22232 Warnings: 0
|