I have a table that has two string columns that repeat a lot, and in the next stage, some counts and conditions need to be done, lets say they are called company_name and person_name. The table has 200k rows.

I am creating a table for companies and persons, each has id, name. I also added company_id_fk and person_id_fk and set FK constraints with nullable default null on the source table.

I have populated the new tables with distinct values from the source table.

Now I am trying to populate the FKs by matching the strings (I couldn't think of any better way!), which looks like this:

UPDATE data_table, companies, persons
SET data_table.person_id_fk = persons.id, data_table.company_id_fk = companies.id
data_table.company_name = companies.`name`
AND data_table.person_name = person.`name`;

This has now been running for 1500 seconds and is still not done. Am I doing this correctly? What are the more effective and correct ways to normalize data_table and add FKs?


1 Answer 1


The solution involved a few changes:

  1. Change all related text type columns into varchar(255)

  2. Add BTREE indexes on all the related columns in all 3 tables (some unique, some not)

  3. Split the queries into two, one for each column

  4. Modify the query to use inner join instead of a cross join:

    UPDATE data_table AS dt INNER JOIN persons as p ON p.name = dt.person_name SET dt.person_id_fk = p.id ;

    UPDATE data_table AS dt INNER JOIN companies AS c ON c.name = dt.company_name SET dt.company_id_fk = c.id ;

This has cut it down to 5-7 secs for each!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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