0

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)

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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