Mysql – Fast migration of field in MySQL

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)

Best Answer

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;

SUGGESTION #1 : Standard Indexing

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    key (myfield)
);

If you index like this, you can either look for the whole string or do left-oriented LIKE searches

SUGGESTION #2 : FULLTEXT Indexing

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    fulltext (myfield)
);

You can effectively use searches for individual keywords as well as whole phrases. You will need to define a custom stopword list because MySQL will not index 543 words.

Here are my other posts from the past two years on FULLTEXT indexes

May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT

SUGGESTION #3 : Hash Indexing

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    hashmyfield char(32) not null,
    primary key (id),
    key (hashmyfield)
);

If you are looking for one specific value and those values could be lengths well beyond 32 characters, you could store the hash value:

INSERT INTO mytable (myfield,hashmyfield)
VALUES ('whatever',MD5('whatever'));

That way, you just search for hash values to retrieve results

SELECT * FROM mytable WHERE hashmyfield = MD5('whatever');

Give it a Try !!!

Mysql – Speeding up a NOT IN subquery

In general you can replace a NOT IN (...) subquery with a NOT EXISTS (...) and the latter will be faster. Logically they are the same though some database optimizers handle the NOT EXISTS better as they can stop the subquery check after the first match.

In contrast, if the DB optimizer isn't smart enough to figure out what your doing and the subquery has a lot of rows, then it must buffer all the rows for each subquery before performing each NOT IN check.

Try out something like this:

SELECT *
FROM table1 a
WHERE NOT EXISTS(SELECT 1
                   FROM table2 b
                  WHERE b.user_id = 2
                    AND b.index_field = a.field1)

In this example the optimizer need only check if a single row exists for the matching subquery, including the additional filter on b.user_id = 2. It should use an index on the index_field and user_id if they are available.

Best Answer

Related Solutions

MySQL – Possible INDEX on a VARCHAR Field

SUGGESTION #1 : Standard Indexing

SUGGESTION #2 : FULLTEXT Indexing

SUGGESTION #3 : Hash Indexing

Mysql – Speeding up a NOT IN subquery

Related Question