I have a flat file transferred to my server every morning via FTP. It is the catalog for our supplier, containing over 300,000 items. I have a PHP script that works exceptionally well for importing it and pairing the fields to match my MySQL database columns. This whole process takes less than a minute.
BUT, the next morning, when its time to update the data, it takes FOREVER (upwards of 40 hours, assuming nothing times out). The script goes line by line through the flat file, finds the associated database record (if there is one) and update that record with the new information. If it doesn't exist in the database, I have to insert it.
There has to be a more efficient way to perform a massive update like this. What am I missing?