how to create index table based on an existing table in Mysql

Question

I have a table contains columns like:

time   tv_program        user_name
xxx    friends           John
xxx    Game of Thrones   Kate
... 
...

Now I want to create separate index table for tv_program and user_name like:

id tv_program
1  friends
2  Game of Thrones
3  ...
4  ...

id user_name
1  John
2  Kate
3  ...
4  ...

and update the original table, replace the tv_program and user_name with the index number instead like

time    tv_program   user_name
xxx     1            1
xxx     2            2
...     ...          ...

Is there any simple way to do this? many thanks!!

E Ciotti · Accepted Answer · 2013-04-12 09:51:57Z

You basically want to normalise your data, and you can do it with MySQL queries:

Create a table tv_programs table with id autoincrement, name index unique

INSERT INTO tv_programs (name) SELECT DISTINCT tv_program FROM original_table

Now you have a table containing the programs, with their id automatically created by MySQL, so you now have to run an UPDATE with join (using string names, could be slow if you have lots of data and no index, but you just run it once) and update the original table with the id of the joined table (tv_programs)

UPDATE original_table ot LEFT JOIN tv_programs tp ON ot.tv_program = tp.name SET ot.tv_program = tp.id

And now you can change the type of tv_program column to INT

Repeat with the other table. Do backups before, or add a column to the original table (e.g. tv_program_id) and update that one with IDs, so that you can run other queries to check the results before dropping the old column and continue

Hope that's clear.

Collectives™ on Stack Overflow

how to create index table based on an existing table in Mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related