Creating MEMORY table with varchar in MySQL

Question

I need to create TEMPORARY tables for processing (via JOIN) a large set of data (1M rows), which have varchar columns. If I use ENGINE=MEMORY, it will change varchar to char. Then, the data will not fit into memory (even by increasing tmp_table_size/max_heap_table_size).

I understand that memory mapping need char structure, but is there a workaround to create TEMPORARY tables in MEMORY using varchar to minimise the memory usage?

This is my process:

CREATE TEMPORARY TABLE temp
(
ArticleID int(11) unsigned NOT NULL,
Tag varchar(255),
INDEX(Tag),
PRIMARY KEY(ArticleID,Tag)
) ENGINE=innoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

LOAD DATA LOCAL INFILE 'file.csv' IGNORE INTO TABLE temp 
  FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' 
  (ArticleID,Tag);

// Adding missing tags in tag table
INSERT IGNORE INTO Tags (Tag) SELECT DISTINCT Tag FROM temp;

INSERT INTO TagMap (ArticleID,TagID) 
  SELECT a.ArticleID,b.TagID FROM temp a JOIN Tags b ON a.Tag=b.Tag;

In my experience, InnoDB has a better performance as compared with Aria and MyISAM.

Answer 1

You will be better off not 'normalizing' tags.

CREATE TABLE Tags (
    ArticleID int(11) unsigned NOT NULL,
    Tag varchar(255),
    INDEX(Tag),
    PRIMARY KEY(ArticleID,Tag)
) ENGINE=innoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

LOAD DATA ... INTO Tags ...;

Skip the rest of your steps.

Now simplify the code elsewhere since you don't need to go through TagMap to get to a tag.

And there is no need for MEMORY. (And possibly no advantage, anyway.) Nor a SHA1 kludge.

Note that INDEX(Tag) will be "covering" when Joining from Tag to ArticleID. This is because InnoDB tacks the PK onto the end of each secondary index.

(I did not follow your "missing tags" issue, but it might be simpler now that there is only one new table.)

Example from Comments -- List articles have all of 3 specific tags:

SELECT a.*
    FROM ( SELECT articleID FROM Tags
             WHERE tag IN ('foo', 'bar', thing')
             HAVING COUNT(*) = 3
         ) AS t
    JOIN Articles AS a USING(articleID);