Re: faster update
In the last episode (Jul 23), Greg D. said: >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'"; Everything's a tradeoff. Indexes slow down inserts and updates because the index must be maintained, but the advantage of being able to directly access particular records with them instead of doing a full table scan more than offsets it. Add an index on email and be happy. -- Dan Nelson [EMAIL PROTECTED] - 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
Hi. On Tue 2002-07-23 at 08:35:29 -0700, [EMAIL PROTECTED] wrote: > 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. You should index the column if such queries run often or if they do not, but you really need a speed improvement. The statement that indexes slow down updates refers to the fact, that on each update/insert/replace, each index has to be updated. That means, that an additional index will speed up the update in question by magnitudes of order, but will slow down all (other) updates a bit in exchange. It is a trade-off and the question is simply if you need the speed for the update in question bad enough. In my personal experience, the additional disk usage of new indexes was more of a problem than any speed decrease. YMMV. Greetings, Benjamin. > > my update statement: > UPDATE table SET name='NULL' WHERE email='$email'"; -- [EMAIL PROTECTED] - 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
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
Re: faster update
Hello Greg,
maybe it can be of some help:
- Try ( if memory is not a problem ) to read the entire file in memory,
inside an array, and use foreach() in the array. It is quicker than
reading line by line from the HD;
- And you may try to mount your UPDATE statement to update a bunch
of emails at once, using 'WHERE email IN( email1, email2, ..., emailN )'.
You can try with 1000 or 1 at a time, mounting the statement and
executing after you have a list of emails, instead of using bind values.
This may or may not be of some help.
On Thursday 06 June 2002 12:51, Greg D. 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 (){
>
> print "$_\n";
> $sth->execute($_) || die "Error2 executing update statement on
> track:\n" . $sth->errstr;
> }
> close A;
--
João Paulo Vasconcellos
Gerente de Tecnologia - NetCard
Tel. 21 3852-9008 Ramal 31
[EMAIL PROTECTED]
-
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
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 (){
>
> 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
Re: faster update
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 (){
> >
> > 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
Re: faster update
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 (){
>
> 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.
-
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
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 (){
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
