MySQL table split

Question

I have a table with about 20 columns. 10 of them generally tend to have 1 of 15 or so possible values (different for each column). In addition, one column has a largish string. The table currently has over 3 million rows, and growing. It is about 1GB big (just data)

I would like to split off the large text column, since it is not used often and would probably dramatically reduce the table size, thus improving performance.
I would like to "normalize" all those repeating columns into a separate table (each), so that I can get the list of current values without running 10 distinct queries on 3M rows. It takes a long time.

#2 would be one-to-many relations. #1 could be 1-to-1 or 1-to-many. I don't care.

The question is: Can these be done by pure SQL statements? How? Or do I need to write a program to put the data in the new table and get the PK and insert it in the right column, one row at a time?

Edit
Here is a sample of what I am trying to do:

ID  Field1  Lookup  Text
10  val1    look1   some very long text
11  val2    look2   more very long text
12  val2    look1   NULL
13  val4    look1   some very long text
.
.
.

To this:

ID  Field1  Lookup  Text
10  val1    1       1
11  val2    2       2
12  val2    1       0
13  val4    1       4 (1?)
.
.
.

rlanvin · Accepted Answer · 2012-02-23 13:40:54Z

1

For the point 1, it's a one-to-one relationship, so you don't need a foreign key (instead, you'll use the same primary key for both table).

Create your table mytable_text with a PK of the same type (not in auto increment of course), your TEXT column, and, if you're using InnoDB (which is recommend in this case) a foreign key to your first (main) table. You could add ON DELETE CASCADE to the foreign key to ease the maintenance.
INSERT INTO mytable_text (id, large_text_column) (SELECT id, large_text_column FROM mytable)
ALTER TABLE mytable DROP large_text_column

For the point 2, it's a bit longer, but it's also doable in SQL (I'm taking the example of your field "Lookup"). It could be something like that:

Create your secondary table "lookups" with an autoincrement ID, and a field "title" for example (with a UNIQUE INDEX if you feel like it).
INSERT INTO lookups (title) (SELECT DISTINCT Lookup FROM mytable)
SET foreign_key_checks = 0
ALTER TABLE mytable ADD lookup_id INTEGER UNSIGNED [...], ADD FOREIGN KEY [...]
UPDATE mytable SET lookup_id = (SELECT id FROM lookups WHERE lookup.title = mytable.Lookup
ALTER TABLE mytable DROP Lookup
(optionally) ALTER TABLE mytable CHANGE lookup_id Lookup [...] (if you want to keep the same name)
SET foreign_key_checks = 1

edited Feb 23, 2012 at 13:40

answered Feb 23, 2012 at 13:05

rlanvin

6,2872 gold badges20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rlanvin Over a year ago

I edited my answer based on your example. Note that with 3M rows the alter statements can take some time.

Collectives™ on Stack Overflow

MySQL table split

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related