CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
UPDATE ALOT!

 



PerlJam
Novice

Apr 4, 2002, 12:20 AM

Post #1 of 5 (6143 views)
UPDATE ALOT! Can't Post

Hello,

I have to update 2566 records in a MySQL DB.
I have done this b4 and it killed MySQL and took 3 hours. Is there a faster way to do this....


Code
 foreach $email (@file) { 

chomp $email;



my $qy = "UPDATE PDmain SET status ='active' WHERE email = '$email'";

$dbh->do($qy);





}



Thats not good eh ? whats the best way to program this ..
hope you can help, Thanks,

Drew


uatt
User

Apr 4, 2002, 10:21 PM

Post #2 of 5 (6140 views)
Re: [PerlJam] UPDATE ALOT! [In reply to] Can't Post

I don't find any wrong with this code...


PerlJam
Novice

Apr 4, 2002, 11:40 PM

Post #3 of 5 (6139 views)
Re: [uatt] UPDATE ALOT! [In reply to] Can't Post

Sorry. There is nothing wrong with it. It just uses up 90% of my resources on my server and slows my site down. Maby thats the only and best way to do it.


skirrow
stranger

Apr 7, 2002, 3:55 PM

Post #4 of 5 (6136 views)
Re: [PerlJam] UPDATE ALOT! [In reply to] Can't Post

Hi,

Is this not a good thing to do? I will be transferring my member's database of 20,000+ users to mySQL soon. Is that going to be a problem for mySQL and the server?

It's a 1.4Ghz, 1Gb RAM server, surely it will be okay?

- Neil
--
http://www.locked-area.com/
http://www.opencrypt.com/


Danni
Novice

Apr 25, 2002, 5:53 PM

Post #5 of 5 (6120 views)
Re: [PerlJam] UPDATE ALOT! [In reply to] Can't Post

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...



Code
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...


Code
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


 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives