any size limitation as to the size of a query statement?
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
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
