I need to assign random
IDs to a table. Thus, I create a mapping table as
CREATE TABLE t2 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, SourceID int(11) unsigned NOT NULL, UNIQUE INDEX(SourceID), PRIMARY KEY(ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1
and then added the
IDs from the main table
INSERT IGNORE INTO t2 (SourceID) SELECT ID FROM t1 ORDER BY RAND()
For example, imagine
t1 is the test results of the students, and we do not want to reveal the student ID (t1.ID) to reviewers (for an anonymous review). Then, we show each record with a new ID stored in
SELECT t2.ID AS NewID, t1.results FROM t1 JOIN t2 ON t1.ID=t2.SourceID
The problem is
t1 is tens of millions of rows and
RAND() is very very slow.
I do not need a perfect
RAND() here; just assigning new
IDs (somehow randomly arranged). Can you think of an approach to do query faster?