Mysql UPDATE - Is this the fastest way?

[bsnrjones]

I have two tables. Table A gets the current stock level updated from Table B (table b is filled with the current stock level from distributor).

Is this statement the fastest way to accomplish this?

UPDATE products,products_temp SET products.products_quantity=products_temp.stock WHERE products.products_model=products_temp.sku;

When I run this my OsCommerce site completely stops running until the update is finished. The products table has about 4,500 lines, and the products_temp table has about 6,400 lines.

It takes about 3 minutes to update - so I am sure I have customers leaving. I am currently running this once per hour to keep my inventory levels current with my distributor.

Any thoughts?

[httpwebwitch]

ooh, no no no

there has to be a better way. Don't waste so much time copying data from one table to another - leave it where it is. Is there a reason you need two copies of the same data?

Pretend you have two books open in front of you. One is a recipe book, and the other is blank notebook. When you want to read how to make pancakes, do you copy the recipe into your blank book before you read it? No, you just look at the other book, it's already in there!

It may just require some rewriting so that your scripts look in "products_temp" instead of "products", but imagine how much time will be saved!

This is being used in conjunction with your text file with the whole "quote" stripping problem, isn't it!

It is OK to use two tables :-)

[ExpLarry]

Any thoughts?

Do you have indexes on the fields referred to in the WHERE clause?

[bsnrjones]

Two responses:

The reason I have two files is because products table is OsCommerce. My products_temp is simply where I grab a file via ftp from my distributor that has SKU's and stock quantities then feed them into this table so I can do the update to the products table.

httpwebwitch: Yes this is used in conjunction with my other question from earlier today. Your right, I might be able to find all instances in OsCommerce and just pull the stock levels from the product_temp table. Don't know how familiar you are with OsCommerce, but these references could be spread over dozens of files. Still that might be the best route to go.

explarry: There are a couple of indexes assigned in the products table, but none have been assigned in the products_temp table. Also the indexes are not assigned to fields that I need to match in the where clause.

Burke

[timster]

Does table B have a timestamp, so you'd only update rows that had changed since the last update?

Updating a couple thousand rows should only take a couple seconds, if everything's set up OK.

Here is some food for thought:

[c2.com ]
[nooron.org ]