Results 1 to 2 of 2
Dec 18, 2004, 14:23 #1
- Join Date
- Dec 2004
- 0 Post(s)
- 0 Thread(s)
Need help on best way to insert many items quickly
My client has a product database od around 20000 items. And it is updated every few days when he gets a catalog from the supplier.
It's supposed to work like this: if there already is a product in the database with that ID (primary key), just UPDATE the price, and if there is no product with that ID it should do an INSERT for that item.
Now I've tried it in several ways and I can't seem to get it to execute in under 20 seconds (max execution time is 30s, so just to be sure).
I've tried doing an update for every item (UPDATE product SET price=$price where ID=$id) and if I get a mysql_affected_rows()==0 (a PHP function that returns the number of affected rows) then I do an insert. This takes too long for 20000 items.
I've also tried collecting all IDs in one array and then doing an in_array() check (in_array() in PHP checks whether a value is in array) for every ID value, and if it exists in the array I do an update in MySQL, and if not I do an insert. This also takes too long (although less than the first method).
Of course I can have the script redirect to itself after 20 seconds and continue from where it left off, but this seems kind of lame.
Since the client wants to enter extra data for products and not lose it when he does a price update, I can't just replace everything on every update, just the price.
Changing the hosting is out of the question (he paid 2 years in advance and has like 100000 emails set up in the control panel).
I'm sorry if this does not belong in the databases forum, should I post it to PHP? And sorry for the explanations of PHP functions, just in case...
Dec 18, 2004, 15:37 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)