I'm a bit confused by what you mean here; are you just updating the
'ktjnews' column, or are you attempting to reset the 'email' column as well?
(The source says the first, your comments suggest the second.)
Further, exactly which part of this is too slow? Obviously, each query will
require scanning the entire table unless you've got an index with 'email' as
the first field. (If there is some reason you're not doing that, you should
address that reason, not try to work around not having the index.)
Again, I can't entirely reconcile your comments with the source: where are
you getting the number 800,000? Is that the number of entries in the
database, or is it the number of lines in the file read through A? If you
are really doing 800,000 separate updates against the database, it doesn't
surprise me much if it bogs down the machine a bit.
If this is the case, then I would suggest taking a look at where this A file
is coming from. If instead of getting these 800,000 addresses in file form
you get get them shoved into the database in their own table, you can kind
of hack around the problem by copying your entire table to a temporary one
with a join against your (new) email table and a spiffy test to compute the
new ktjnews field, then replace the contents of the original table with this
temporary table. If the email index on ktj is really a problem, then you can
just index the new email table to optimize the join, and it should take only
one traversal of the ktj table to compute all the values. Further, if you do
actually intend to change the email field (or if the ktjnews field is
indexed) by replacing all the changed rows at once you only have to re-index
the table once. (Is this actually an optimization in MySQL, or is the system
smart enough to make this equally efficient either way?)
Just a few ideas.
-rob
On 6/6/02 at 8:51 am, Greg D. <[EMAIL PROTECTED]> wrote:
> 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
>
>
---------------------------------------------------------------------
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
