加快MySQL PHP查询速度

Question

I have a CSV file of products which need to be added or updated if exists and leave the old ones even if it has been removed from the list.

I have a loop like this:

while (($data = fgetcsv($handle, 1000, ";")) !== FALSE)

Which works just fine, from it I assign few variables like this:

$price = htmlspecialchars(mysql_real_escape_string($data[2]), ENT_QUOTES, 'utf-8' );

After I have set 4 variables I need, I query MySQL like this:

mysql_query("SELECT * FROM products WHERE productid = '$prodid' AND supplier = 'SUPPLIER1'")

If it results 1 or more we just update it

            mysql_query("UPDATE tuotteet SET 
                        supplier = 'SUPPLIER1',
                        product = '$product',
                        prodid = '$prodid',
                        ean = '$ean',
                        price = '$price' WHERE prodid= '$prodid' AND supplier = 'SUPPLIER1'") or die(mysql_error());

If product not found from database, we make another query INSERT. Problem is that this is a very slow way to do this, it takes many many minutes to go through about 10000 productlines.

Anything to do with this?

Answer 1

Before starting the process I would query the database for all the product_id's in the database, then I would store them in a dictionary.
Then, start processing the csv file. For every record, just ask the dictionary if it has a key with the current product_id.

In doing so, you avoid having to go to the database for every record you have in the csv.