CODEWITHSUNDEEP
mysqlsql
Nirojan Selvanathan
Nirojan Selvanathan

Reputation: 11164

Join and update take too long

I have two tables FACT_SALE and DIM_PRODUCT which both have the product name as common and I wanted to set the DIM_PRODUCT's ID as a foreign key. DIM_PRODUCT is created with the distinct name values of the FACT_SALE table.

UPDATE FACT_SALE FS
JOIN DIM_PRODUCT DP ON FS.PRODUCT_NAME = DP.NAME
SET FS.PRODUCT_KEY = DP.ID

This query I wrote runs forever and no changes are reflected in the DB too. There is only one hundred thousand records (100 000) in the FACT_SALE table. But if I run a select query with Joining two table it runs lightning fast.

Can I know what is the root cause for this issue ?

Upvotes: 0

Views: 65

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522431

Do you have an index on either of the join columns FACT_SALE.PRODUCT_NAME or DIM_PRODUCT.NAME? If you have indices on either of these columns could slow down the update because the index would have to be updated along with the data.

From this SO question an index would only benefit columns in the WHERE clause. But since you don't have a WHERE clause, an index might hurt and not help.

Upvotes: 1

Sagar Gangwal
Sagar Gangwal

Reputation: 7980

UPDATE FACT_SALE FS
INNER JOIN DIM_PRODUCT DP ON FS.PRODUCT_NAME = DP.NAME
SET FS.PRODUCT_KEY = DP.ID

First of all,replace join with INNER JOIN.

Second thing you have to create index on column which you are using ON clause in JOIN condition.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

For this query:

UPDATE FACT_SALE FS JOIN
       DIM_PRODUCT DP
       ON FS.PRODUCT_NAME = DP.NAME
SET FS.PRODUCT_KEY = DP.ID ;

You want an index on DIM_PRODUCT(NAME, ID):

CREATE INDEX IDX_DIM_PRODUCT_NAME_ID ON DIM_PRODUCT(NAME, ID);

Upvotes: 4

Related Questions