CODEWITHSUNDEEP
mysqlinner-join
James
James

Reputation: 11

Better way of copying data?

I have two tables where I want to copy the post_id from one table to another when the testpostmeta.meta_value = testTable.stockcode

There's about 2000 rows in testTable and 65k rows in testpostmeta.

The code works, it just takes about 1-2 minutes to complete. Is there anything that can be done to speed the hamster wheel up?

UPDATE testTable 
  INNER JOIN testpostmeta 
    ON testTable.stockcode = testpostmeta.meta_value
   SET testTable.post_id = testpostmeta.post_id

I tried adding WHERE testpostmeta.meta_value = testTable.stockcode but that didn't work.

Upvotes: 1

Views: 48

Answers (4)

alimbaronia
alimbaronia

Reputation: 504

Try adding an index to each table that matches the field used for your JOIN criteria:

ALTER TABLE testTable ADD INDEX stockcode_idx(stockcode);
ALTER TABLE testpostmeta ADD INDEX meta_idx(meta_value);

Upvotes: 1

Paul T. Rawkeen
Paul T. Rawkeen

Reputation: 4114

If post_id is indexed in target table that also can slow down the update.

Try disabling index before the operation and enable it after. So you data will be indexed once rather on each subsequent data change.

ALTER TABLE targetTable DISABLE KEYS;
-- Your UPDATE query
ALTER TABLE targetTable ENABLE KEYS;

And as said in the reference:

Performing multiple updates together is much quicker than doing one at a time if you lock the table.

Here some reference page that can give more idea on what can be done:

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

be sure you have proper indexes on testTable and testpostmeta 

CREATE INDEX my_idx1  ON testTable (stokcode);

CREATE INDEX my_idx2  ON testpostmeta (meta_value , post_id);

Upvotes: 1

Deepak Kumar
Deepak Kumar

Reputation: 668

You can stop the autocommit

SET autocommit = 0 ;

--Insert/Update/Delete stuff here
COMMIT ;

Upvotes: 0

Related Questions