Fast migration of field in MySQL Right now I have the following tables items


name (varchar)
category (varchar)
id


...etc

I want to create a categories table and items_categories mapping table so i can start tracking categories in its own table.

The problem is with a large items table it takes awhile

Right now I do select distinct category on items table. That is going to be slow and there really isn't a way around it.

What is the fastest way to migrate the category field in items to have a category table and a categories items table? This has to be done in all Sql (no external scripts)
Caveat:  This is untested code.

CREATE TABLE Categories (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    category VARCHAR(...) NOT NULL,
    PRIMARY KEY(id),
    INDEX(category)
) ENGINE = InnoDB;

-- Create mapping table
CREATE TABLE Categories_Items (
    category_id SMALLINT UNSIGNED NOT NULL,
    item_id ...,
    PRIMARY KEY(category_id, item_id),
    INDEX(item_id, category_id)
) ENGINE = InnoDB;

-- Populate Categories table and generate ids
INSERT INTO Categories (category)
    SELECT DISTINCT category FROM items;

-- Start to convert Items.category to Items.category_id
ALTER TABLE Items
    ADD COLUMN category_id SMALLINT UNSIGNED NOT NULL;

-- Populate mapping table
INSERT INTO Categories_Items
   (category_id, item_id)
    SELECT c.id, i.id
        FROM Categories AS c
        JOIN Items AS i  ON i.category = c.category;

-- Finish conversion
ALTER TABLE Items
    DROP COLUMN category;