Making faster MySQL PHP queries

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?

You could try pre-caching data you know, then do the comparision using PHP from the pre-cached data, and update if needed using a bulk query. — Diamondo25
– Diamondo25, Commented Mar 20, 2014 at 14:56
Why not use INSERT.... ON DUPLICATE KEY.... rather than SELECT followed by INSERT or UPDATE? — Mark Baker
– Mark Baker, Commented Mar 20, 2014 at 14:56

Esteban 8A · Accepted Answer · 2014-03-20 14:58:38Z

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.

answered Mar 20, 2014 at 14:58

Esteban 8A

334 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Bechard Over a year ago

I like this approach, combined with Diamondo25's recommendation for a bulk query. See accepted answer here for an example of how to a bulk INSERT: stackoverflow.com/questions/12960569/mysql-bulk-insert-via-php

Bill Karwin Over a year ago

This works unless the size of the CSV file exceeds your PHP max memory limit.

Esteban 8A Over a year ago

You're right, and if thats the case you can always change it on php.ini.

score 0 · Accepted Answer · 2014-03-20 22:12:18Z

0

I had a similar problem. The best solution is to build a single-query. Here is the code in Python:

# data to updload in a dictionary {id, new_value}
dict = {2:1001,4:251}

ids = [] 
sql = "UPDATE tabla1 SET value = CASE id "    

for key in dict:
    sql = sql + 'WHEN '+str(key)+' THEN '+str(dict[key])+ '\n\t'
    ids.append(key)
ids_string = ', '.join(map(str, ids))
sql = sql + "END WHERE id IN ("+ids_string+")"
print sql

edited Mar 20, 2014 at 22:12

answered Mar 20, 2014 at 15:04

user3441996

6 Comments

Dan Bechard Over a year ago

I would use the phrase "one solution" as opposed to "the best solution".

user3441996 Over a year ago

so tell me, what do you think is the best solution? or a better one?

Dan Bechard Over a year ago

"Best solution" is not a term anyone should use when answering such an open-ended question, because there is no clear-cut best. I think Esteban's ID cache is better than a dynamic SQL approach. I also think your single-query solution will fail miserably for anything more than a hundred or so records due to bandwidth and timeout constraints.

Dan Bechard Over a year ago

Your code example is also quite flawed. It doesn't compile in Python nor does it produce sane SQL. In particular, the line sql = sql + "WHEN %d THEN %d ", key, key makes no sense. If you could fix your example so that it compiles and post some sample output that would be helpful.

user3441996 Over a year ago

Sorry that line was commented, just comment it again and everything will work. (it's edited) I have tested it with hundreds of records and works fine. When we want to update thousands of rows(which is quite improbable), we can divide it in some 'single queries'

|

Bill Karwin · Accepted Answer · 2014-03-20 22:20:16Z

0

It would be a lot faster, more efficient, and require less code to use LOAD DATA:

LOAD DATA LOCAL INFILE 'myinput.csv'
 REPLACE INTO TABLE tuotteet

You just have to make sure you have a PRIMARY KEY or UNIQUE KEY defined over columns (prodid, supplier). The LOAD DATA will use that to tell if the row already exists and needs to be updated, or if the row doesn't exist and needs to be inserted as a new row.

Then you don't need to use fgetcsv(), you don't need to do any escaping, you don't need to SELECT or UPDATE or INSERT, and it should run about 10x faster.

answered Mar 20, 2014 at 22:20

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Collectives™ on Stack Overflow

Making faster MySQL PHP queries

3 Answers 3

3 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest