faster update

[Greg D .]

Hello, 

 I was wondering if there was anyway for me to speed up my update query.
I wanted to index the field that i was searching for., but i heard that 
indexing slows down updates or that some information might not work...is that 
true? thanks for any help.

my update statement:
UPDATE table SET name='NULL' WHERE email='$email';

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

faster update

[Greg D .]

Hello, 

I was wondering if there was a better way to speed up
my update statement in my perl script.  It goes through all the
entries in the database , which is around 800, 000, and then updates the email
field.  The problem is that it takes to long.
thanks in advance for any help

here the code:


open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
 
my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update 
statement on track:\n . $dbh-errstr;
 
while (A){
   
print $_\n;
$sth-execute($_) || die Error2 executing update statement on 
track:\n . $sth-errstr;
}
close A;

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: faster update

[Greg D .]

On Thursday 06 June 2002 08:53 am, you wrote:
 On Thursday 06 June 2002 9:51 am, Greg D. wrote:
  I was wondering if there was a better way to speed up
  my update statement in my perl script.  It goes through all the
  entries in the database , which is around 800, 000, and then updates the
  email field.  The problem is that it takes to long.
  thanks in advance for any help
 
  here the code:
 
 
  open(A, $ktjpath/$ktjlist) || death(Can't open $ktjlist: um121);
 
  my $sql_statement = UPDATE ktj SET ktjnews = 'N' WHERE email = ?;
  my $sth = $dbh-prepare($sql_statement) || die Error1 preparing update
  statement on track:\n . $dbh-errstr;
 
  while (A){
 
  print $_\n;
  $sth-execute($_) || die Error2 executing update statement on
  track:\n . $sth-errstr;
  }
  close A;

 Please note:  If you are using this to maintain a list of emails that you
 will send spam to (an 'opt-in' mailing list does NOT count but an 'opt-out'
 does), you may not read any further in this message and may not use any
 advice I give.  :)

 Are you indexing on email?  If not, you should be.  That should reduce this
 from a table scan to an index scan provided that the emails are mostly
 unique.

I tried indexing email already but then when i went back to check if the 
update was working a lot of the fields were not updated.

thanks though
greg

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php