Danni
Novice
Apr 25, 2002, 5:53 PM
Post #5 of 5
(13153 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 |
MAIN
SEARCH
WHO'S 
LOG 