Re: any size limitation as to the size of a query statement?
Why not do something like:
my $q = DELETE FROM master WHERE master_id NOT IN (;
$q .= join(',', @masteridarray);
$q .= ')';
$dbi_dbh-do($q);
Though this will obviously fall into the same size limitations that you were
doing, but it should execute a lot faster, and be a bit smaller than your
example. (I don't have an answer for this, being rather new to the mysql
community myself).
-Chris
Hello,
I have a table with approximately 68,000 records. I then get a file
uploaded to the server with approximately 68,000 master_id's listed in it.
If the master_id's in our MySQL table are not one of the master_id's listed
in the file, they need to be deleted from the MySQL table.
Right now, a Perl script reads the master_id's from the uploaded file, and
puts them all into an array in memory. We then loop through all the
master_id's from the MySQL table. If the MySQL master_id is not in the
array of master_id's from the file, then the record is deleted. This works
fine if we are using a small amount of records, but there are over 68,000 of
them. It seems like there should be a better, more efficient way of doing
this.
One thought that I had, was to create one big huge DELETE statement.
Something like this:
$q = DELETE FROM master;
$where = WHERE ( ;
$morethan1 = 0;
foreach (@masteridarray) { #this is our 68,000 valid id's
if ($morethan1) {
$where .= OR master_id != $_ ;
}else{
$where .= master_id != $_ ;
$morethan1 = 1;
}
}
$where .= );
if ($morethan1) {
$q .= $where;
}
my $sth = $dbi_dbh-prepare($q);
$sth-execute;
However, I wasn't sure if MySQL could process something like this. And if
it could, how much bigger could the query statement get (the number of
records in the table will easily exceed 100,000 before the end of the year,
and continue to grow even bigger next year)?
Another thought/question was whether MySQL had some mechanism for searching
through the upload file itself. Something along the lines of, DELETE FROM
master where master_id != [any of the masterid's in file ./id.txt].
The number of records in this table is only going to grow over the coming
months, so, I want to make sure that I have a solution that is scalable.
Any/all suggestions will be greatly appreciated!
Mark
Imagine your own dedicated server, but ...
without all the hassles of managing it yourself!
Managed Dedicated Servers
http://www.ServerPros.com
--
Mark A. Sharkey
PrecisionPros.com Network
6543 East Omega Street
Mesa, Arizona 85215
800 844 4434 toll free
480 461 9765 local
480 461 9312 fax
-
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: Re: any size limitation as to the size of a query statement?
My code generates a large query. I had to increase the size of max_allowed_packet so that this query would work. I have this in my /etc/my.cnf file: set-variable = max_allowed_packet=2097152 -bill - 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: any size limitation as to the size of a query statement?
On 21-Jun-01 Chris Petersen wrote:
Why not do something like:
my $q = DELETE FROM master WHERE master_id NOT IN (;
$q .= join(',', @masteridarray);
$q .= ')';
$dbi_dbh-do($q);
Though this will obviously fall into the same size limitations that you were
doing, but it should execute a lot faster, and be a bit smaller than your
example. (I don't have an answer for this, being rather new to the mysql
community myself).
here, @ids is the array, delete in chunks of 64:
while ( my(@id)= splice(@ids,0,64)) {
$qry=DELETE FROM master WHERE master_id in (.join(',',@id).);
SQLQuery($qry);
}
Adjust '64' as needed..
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
-
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: any size limitation as to the size of a query statement?
Thanks, Don. However, this is sort of the opposite of what we need. We
need to delete from master where master_id NOT IN the array. If we do that
in chunks, after the first chunk, we would have wiped out all of the records
outside the array.
It seems like with Chris's suggestion, along with Bill's suggestion about
the max_allowed_packet setting, we should be okay. However, if there are
still more ideas out there, I'd love to hear them!
Mark
-Original Message-
From: Don Read [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 8:46 AM
To: Chris Petersen
Cc: [EMAIL PROTECTED]; Mark A. Sharkey
Subject: Re: any size limitation as to the size of a query statement?
On 21-Jun-01 Chris Petersen wrote:
Why not do something like:
my $q = DELETE FROM master WHERE master_id NOT IN (;
$q .= join(',', @masteridarray);
$q .= ')';
$dbi_dbh-do($q);
Though this will obviously fall into the same size limitations
that you were
doing, but it should execute a lot faster, and be a bit smaller
than your
example. (I don't have an answer for this, being rather new to
the mysql
community myself).
here, @ids is the array, delete in chunks of 64:
while ( my(@id)= splice(@ids,0,64)) {
$qry=DELETE FROM master WHERE master_id in (.join(',',@id).);
SQLQuery($qry);
}
Adjust '64' as needed..
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
-
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: any size limitation as to the size of a query statement?
here, @ids is the array, delete in chunks of 64:
while ( my(@id)= splice(@ids,0,64)) {
$qry=DELETE FROM master WHERE master_id in (.join(',',@id).);
SQLQuery($qry);
}
I thought about this, too.. But Mark said that he needed to delete from the
table where the ID's don't match. If you break it into chunks, then you'd
end up deleting everything that doesn't match that first chunk, which kills
pretty much the entire table.
The other option would be to create a new field in the table, something like
dontdeleteme TinyInt Unsigned Not Null Default 0... Then use your splice
technique to do something like:
$dbh-do('UPDATE master SET dontdeleteme=0');
while ( my(@id)= splice(@ids,0,64)) {
$dbh-do(UPDATE master SET dontdeleteme=1 WHERE master_id in
(.join(',',@id).));
}
$dbh-do('DELETE FROM master WHERE dontdeleteme=1');
I have no idea if this is slower or not, but I do use a similar technique on
a client's database. Granted, that one is about 6800 records, not 68000.
But it's still another thought.
-Chris
-
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: any size limitation as to the size of a query statement?
On 21-Jun-01 Chris Petersen wrote:
here, @ids is the array, delete in chunks of 64:
while ( my(@id)= splice(@ids,0,64)) {
$qry=DELETE FROM master WHERE master_id in (.join(',',@id).);
SQLQuery($qry);
}
I thought about this, too.. But Mark said that he needed to delete from the
table where the ID's don't match. If you break it into chunks, then you'd
end up deleting everything that doesn't match that first chunk, which kills
pretty much the entire table.
I prolly wasn't too clear: @ids is the array of all non-matches.
i.e the list you want to delete ( i'm assuming id is the primary key).
@ids=GetArray(SELECT a.id
FROM master as a LEFT JOIN new_tbl as b ON a.id=b.id
WHERE b.id IS NULL);
The other option would be to create a new field in the table, something like
dontdeleteme TinyInt Unsigned Not Null Default 0... Then use your splice
technique to do something like:
$dbh-do('UPDATE master SET dontdeleteme=0');
while ( my(@id)= splice(@ids,0,64)) {
$dbh-do(UPDATE master SET dontdeleteme=1 WHERE master_id in
(.join(',',@id).));
}
;
Which is _exactly_ how i use it :
# Mark all active webmasters (traffic in the last month)
# without busting max_packet_size
my @ids=GetArray(SELECT distinct id from hits
WHERE hitdate = DATE_SUB(current_date, INTERVAL 1 MONTH));
SQLQuery(UPDATE webmaster set active=0);
while ( my(@id)= splice (@ids,0,64)) {
$qry=UPDATE webmaster set active=1
WHERE wmid in ( .join(',',@id).);
SQLQuery($qry);
}
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
-
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
