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.


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more