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
