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);

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.

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.

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?

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);

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.