Normalizing an existing table with strings data

Question

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
WHERE
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?

Thanks.

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!