mysql - generate 16M unique random numbers -

i'm trying generate 16 000 000 unique random numbers (10-digits: range 1 000 000 000 - 9 999 999 999) , insert them empty table (or fill table if not empty).

the table:

create table `codes` ( `code_id` bigint(20) unsigned not null auto_increment, `code` bigint(20) unsigned not null, `is_used` tinyint(1) not null default '0', primary key (`code_id`), unique key `code` (`code`) ) engine=innodb default charset=utf8 auto_increment=1 ; 

...and function:

delimiter $$  create definer=`root`@`localhost` function `codes`(`minrange` bigint unsigned, `maxrange` bigint unsigned, `_amount` bigint unsigned) returns tinyint(1) modifies sql data begin declare pick bigint;   while (select count(*) codes) < _amount begin set pick = minrange + floor(rand() * (maxrange - minrange + 1)); insert ignore codes (code) values (pick);  end; end while;  return 1; end$$  delimiter ;  -- call: select codes(1000000000,9999999999,16000000); 

the function extremaly slow: generating 20k rows takes 2.5 min., 16m - 33 hours... there way optimise it?

what asking oxymoron. if 16m numbers unique aren't random. think it: 10 digit random number has prob of 1/9e9 being given number. yet 16m'th number has 0 prob of being 1 of 15,999,999 numbers , 1/983,000,001 prob of being 1 of remaining ones. should expect duplicates. there 1% 16m out of 1b.

so suggestion generate (16m + bit) random numbers, unique sort; truncate 16m random sort them. q&d this:

php -r 'for( $i=0;$i<16500000;$i++) echo mt_rand(100000000, 999999999),"\n";'\   | sort -u | head -16000000 | sort -r > /tmp/loadfile.lst 

took on 7 mins on 4 year old laptop. lot faster on modern quad-core desktop. used php-cli because have on laptop , mt_rand routine one. 3 liner-c prog or language hand. zercms says, load quick long disable indexes before load , reenable after. need load column code.

enjoy.