0

I have two tables, both with ~30,000 rows. Anywhere where b.comments is not null, I want to overwrite a.comments with b.comments' data. Seems simple enough - but my query has been running for 45 minutes and I don't think it's working. There about 3,000 values in b that are not null - so a max of 3,000 updates.

I know that syntactically this is fine, but I'm wondering if there is a different way I should be writing this query as I cannot seem to get it to run unless I also add in something like ...and batch_id = 12345 to get the number of records down to 1.

UPDATE temp3 a
INNER JOIN temp2 b
ON a.batch_id = b.batch_id 
and a.batch_li = b.batch_li
SET 
a.comments = b.comments
WHERE
b.comments is not null;
  • 1
    Could you include details of the indexes (or lack thereof) in your question? Might help the accuracy of any answers. – Cowthulhu Apr 29 at 18:32
  • There are currently no indexes on the two tables. Just a primary key on id, but the id fields of the two tables do not relate - which is why I am joining them up by batch_id and batch_li, which creates a match. – Brian Powell Apr 29 at 19:03
  • 1
    Try putting a nonclustered index on both - use batch_id and batch_li as the keys, and make sure to include comments. The order in which you include them depends on the contents of the columns - I'd read up on that before you make a decision. – Cowthulhu Apr 29 at 19:05
  • 1
    that could definitely help, and I hadn't thought of that. Thanks! – Brian Powell Apr 29 at 19:06
  • No problem! Let us know how it goes. To be honest, unless you have a really busy/underpowered DB, I don't know how the query could take 45+ mins even without an index, but hopefully indexes will help. – Cowthulhu Apr 29 at 19:07
1

(Taken from comment)

Try putting a nonclustered index on both - use batch_id and batch_li as the keys, and make sure to include comments. The order in which you include them depends on the contents of the columns - I'd read up on that before you make a decision.

  • More specifically, a 'composite' INDEX(batch_id, batch_li) (in either order). – Rick James May 2 at 0:54
  • @RickJames as I touched upon in my answer, the order will actually matter a fair bit in this sort of index, as shown in this question: stackoverflow.com/questions/2292662/… – Cowthulhu May 2 at 14:10
  • And "index on both" could be misinterpreted as INDEX(batch_id), INDEX(batch_li), which is not as good. – Rick James May 2 at 14:35
  • It is risky to link to Q&A that is not specifically related to MySQL/MariaDB. That link talks about SQL Server and mentions some things that do not apply to MySQL. In a composite index the relative selectivity (or cardinality) does not matter for the query in hand. – Rick James May 2 at 14:41
  • And, to quote one of the answers "For queries that are performing an equality search on all columns in the index there is no significant difference." But, again, it is risky to point to that because the index type was "Hash", which InnoDB does not use. – Rick James May 2 at 14:45
1

The following link is going to explain to you about the cross table update:

How to update 10 million+ rows in MySQL single table as Fast as possible?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.