tag:blogger.com,1999:blog-16959946.post5151386797077783938..comments2023-12-09T16:44:47.897+01:00Comments on The Data Charmer: Pop quiz: generate 1 million recordsGiuseppe Maxiahttp://www.blogger.com/profile/15801583338057324813noreply@blogger.comBlogger28125tag:blogger.com,1999:blog-16959946.post-4896813237680940322007-12-18T15:21:00.000+01:002007-12-18T15:21:00.000+01:00create view v as select 1 from information_schema....create view v as select 1 from information_schema.global_status;<BR/>INSERT t1 SELECT CURDATE()+INTERVAL @a:=IFNULL(@a,0)+1 DAY FROM v,v a,v b LIMIT 1000000;<BR/><BR/><BR/>/ fluppsUnknownhttps://www.blogger.com/profile/01288385577047684762noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-81820231870842273662007-12-18T15:13:00.000+01:002007-12-18T15:13:00.000+01:00use test;drop table if exists t1;create table t1 (...use test;<BR/>drop table if exists t1;<BR/>create table t1 (<BR/> dt datetime not null,<BR/> primary key (dt)<BR/>) engine=myisam;<BR/><BR/>set @@max_allowed_packet=124000000, @a:=729669, @v="(FROM_DAYS(@a:=@a+1))"; <BR/>set @s=concat("INSERT INTO t1 VALUES ",repeat(concat(@v,','),499999), @v);<BR/>prepare p from @s; <BR/>execute p; <BR/>execute p;Unknownhttps://www.blogger.com/profile/04466184994868212679noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-60480816047684137252007-12-18T12:32:00.000+01:002007-12-18T12:32:00.000+01:00Hi!Here's an easy solution. It does the job whith ...Hi!<BR/>Here's an easy solution. It does the job whith just one SQL command, plus a SET command. Yes, the command is more than 76 chars, I know that. You could make it shorter by :<BR/><BR/>use information_schema<BR/><BR/>but still it is more than 75 chars.<BR/><BR/>SET @d = TO_DAYS('2000-01-01');<BR/>insert t1 select from_days(@d:=@d+1) from information_schema.GLOBAL_VARIABLES,<BR/>information_schema.GLOBAL_VARIABLES b,<BR/>information_schema.GLOBAL_VARIABLES c LIMIT 1000000<BR/><BR/>GLOBAL_VARIABLES is of course not a table, but a view, so using it is not against the rules. Don't know the execution time, on my laptop it takes more than a minute, but I guess that is mostly writing to t1 table.Erikhttps://www.blogger.com/profile/07865795210715993795noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-10742745305016811122007-12-18T03:15:00.000+01:002007-12-18T03:15:00.000+01:00insert into t1 values (now()-2),(now()-1),(now()),...insert into t1 values (now()-2),(now()-1),(now()),(now()+(@a:=1));<BR/>insert into t1 select adddate(now(),@a:=@a+1) from t1 a,t1 b,t1 c,t1 d,t1;<BR/>insert into t1 select adddate(now(),@a:=@a+1) from t1,t1 b limit 998972;jedyhttps://www.blogger.com/profile/04289869955957083884noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-54910222221292558772007-12-18T01:36:00.000+01:002007-12-18T01:36:00.000+01:00Hey gmax,Another solution for your quiz! quite a h...Hey gmax,<BR/><BR/>Another solution for your quiz! quite a hack though, but still hoping for some fame and fortune ;)<BR/><BR/>Matthias<BR/><BR/><BR/>TRUNCATE t1;<BR/>/* the solution */<BR/>/* dates with these numbers are valid and are converted to 2000-04-XX */<BR/>SET @a=420,@b=421,@c=422,@d=423,@e=424,@f=425,@g=426,@h=427,@i=428,@j=429;<BR/>INSERT INTO t1 VALUES(@a),(@b),(@c),(@d),(@e),(@f),(@g),(@h),(@i),(@j);<BR/>/* add 10*10*10 rows = 1010 rows in the table after that */<BR/>INSERT INTO t1(dt) SELECT FROM_UNIXTIME(@j:=@j+1)FROM t1 JOIN(t1 a,t1 b);<BR/>/* remove the first 10, reset the incrementing var to the value before inserting last 1000 (these records need to be replaced) */<BR/>DELETE FROM t1 WHERE dt IN(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j:=429);<BR/>/* start inserting all over again, insert 1000*1000 new rows, the first 1000 will be replaced */<BR/>REPLACE INTO t1(dt) SELECT FROM_UNIXTIME(@j:=@j+1)FROM t1 JOIN (t1 a);<BR/>/* end solution */<BR/>SELECT COUNT(*) from t1;Unknownhttps://www.blogger.com/profile/09617676457836072157noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-37574389031262599752007-12-18T00:48:00.000+01:002007-12-18T00:48:00.000+01:00my first attempt:insert into t1 values (now()),(no...my first attempt:<BR/><BR/>insert into t1 values (now()),(now()-1),(now()-2),(now()-3),(now()-4);<BR/>insert into t1 select from_unixtime(if(@a,@a:=@a+1,@a:=0)) from t1;<BR/>insert into t1 select from_unixtime(@a:=@a+1) from t1 a,t1 b, t1 c;<BR/>insert into t1 select from_unixtime(@a:=@a+1) from t1 a,t1 b limit 998990;Domas Mituzashttps://www.blogger.com/profile/00843761075650946974noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-90661334709338401732007-12-18T00:13:00.000+01:002007-12-18T00:13:00.000+01:00I'm not sure if this is ok with the rules.I'm usin...I'm not sure if this is ok with the rules.<BR/><BR/>I'm using t1, but no other existing tables (not sure if it says that not inserting from tables include t1?)<BR/><BR/>Otherwise this should give you 1.86320859201e+15 rows with three commands (unless it runs out of range for DATE before then - I couldn't finish this on my laptop within a reasonable time, but even if it runs out of range, you could use MyISAM and still have > 1M rows! engine isn't specified in the rules).<BR/><BR/>INSERT t1 VALUES(101),(102),(103),(104),(105),(106),(107),(108),(@a:=109);<BR/><BR/>INSERT t1 SELECT CURDATE()+INTERVAL @a:=@a+1 DAY FROM t1,t1 t2,t1 t3,t1 t4;<BR/><BR/>INSERT t1 SELECT CURDATE()+INTERVAL @a:=@a+1 DAY FROM t1,t1 t2,t1 t3,t1 t4;<BR/><BR/>it's probably not as fancy as you're looking for, more brute force...<BR/><BR/>I'll see if I can find a more clever solution later ;)Unknownhttps://www.blogger.com/profile/01288385577047684762noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-37867727502562297712007-12-17T23:08:00.000+01:002007-12-17T23:08:00.000+01:00insert into t1 values(@d:=10000104),(@d+@i:=1),(@d...insert into t1 values(@d:=10000104),(@d+@i:=1),(@d-1),(@d-2),(@d-3);<BR/>insert into t1 select date_add(@d,interval(@i:=@i+1)day) from t1,t1 a,t1 b;<BR/>insert into t1 select date_add(@d,interval(@i:=@i+1)day) from t1,t1 a,t1 b;<BR/>delete from t1 limit 1197130;rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-29075030087981555912007-12-17T23:00:00.000+01:002007-12-17T23:00:00.000+01:00set max_allowed_packet := 1073741824;set @s=concat...set max_allowed_packet := 1073741824;<BR/>set @s=concat('insert into t1 values(@d:=10000101+@i:=1)');<BR/>set @s=concat(@s,repeat(',(date_add(@d,interval @i:=@i+1 day))',999999));<BR/>prepare s from @s;<BR/>execute s;rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-39433877921185512102007-12-17T22:57:00.000+01:002007-12-17T22:57:00.000+01:00# here is my solution in 4 lines instead of 5SET @...# here is my solution in 4 lines instead of 5<BR/>SET @x = 366;<BR/>INSERT INTO t1 VALUES(NOW()-9),(NOW()-8),(NOW()-7),(NOW()-6);<BR/>INSERT INTO t1 SELECT FROM_DAYS(@x:=@x+1) from t1,t1 b,t1 c,t1 d, t1 e;<BR/>INSERT INTO t1 SELECT FROM_DAYS(@x:=@x+1) from t1,t1 a limit 998972;Dirk1231https://www.blogger.com/profile/01604966231936807391noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-60102713005105666572007-12-17T22:44:00.000+01:002007-12-17T22:44:00.000+01:00Competitive side got the better of me...insert t1 ...Competitive side got the better of me...<BR/><BR/>insert t1 values(101),(102),(103),(104),(105),(106),(107),(@c:=now());<BR/><BR/>insert t1 select @c:=@c+interval 1 second from t1,t1 b,t1 c,t1 d,t1 e,t1 f;<BR/><BR/>insert t1 select @c:=@c+interval 1 second from t1, t1 b limit 737848;<BR/><BR/>-DipinAnonymoushttps://www.blogger.com/profile/17903278771915130754noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-55004494757428414972007-12-17T21:52:00.000+01:002007-12-17T21:52:00.000+01:00Anyone else get it with one sql statement yet? a...Anyone else get it with one sql statement yet? and if you really think using information_schema is cheating the rule about no other tables (which it isn't because everyone has those even on "empty" servers), you can do the same exact thing here by creating 100 records via a 10x10 union, cross joining that 3 times, and deleting the original 100.<BR/><BR/>insert into t1 select date_add('2000-01-01', interval @c0:=@c0+1 second) from (select @c0:=0, @c1:=0, @c2:=0, @c3:=0) dummy cross join (select @c1:=@c1+1 from information_schema.global_variables limit 100) q1 cross join (select @c2:=@c2+1 from information_schema.global_variables limit 100) as q0 cross join (select @c3:=@c3+1 from information_schema.global_variables limit 100) as q3;ryanhttps://www.blogger.com/profile/04325518011075898953noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-60393368613313455002007-12-17T21:51:00.000+01:002007-12-17T21:51:00.000+01:00My vertion:set @c=null,@f=now();INSERT INTO t1 SEL...My vertion:<BR/><BR/>set @c=null,@f=now();INSERT INTO t1 SELECT @f:=DATE_ADD(@f,INTERVAL<BR/>1 SECOND) FROM (SELECT c FROM (SELECT @c:=IF(IFNULL(@c,0)=0,<BR/>1,@c+1) c FROM mysql.help_relation x CROSS JOIN mysql.help_relation y<BR/>CROSS JOIN mysql.help_category z) d WHERE c<=1000000) d1Unknownhttps://www.blogger.com/profile/00409986569937662287noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-36386507241572867762007-12-17T21:21:00.000+01:002007-12-17T21:21:00.000+01:00# sorry about the last submit... i thought i had r...# sorry about the last submit... i thought i had removed the reference<BR/># to the second table<BR/>insert into t1 values(NOW()-10),(NOW()-9),(NOW()-8),(NOW()-7),(NOW()-6);<BR/>insert into t1 values(NOW()-5),(NOW()-4),(NOW()-3),(NOW()-2),(NOW()-1);<BR/>SET @x = 729669;<BR/>insert into t1 SELECT FROM_DAYS(@x:=@x+1) FROM t1,t1 a,t1 b,t1 c,t1 d,t1 e;<BR/>delete from t1 limit 10;Dirk1231https://www.blogger.com/profile/01604966231936807391noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-59482527056652660632007-12-17T20:53:00.000+01:002007-12-17T20:53:00.000+01:00# here is the solution with a single tableinsert i...# here is the solution with a single table<BR/>insert into t1 values(NOW()-10),(NOW()-9),(NOW()-8),(NOW()-7),(NOW()-6);<BR/>insert into t1 values(NOW()-5),(NOW()-4),(NOW()-3),(NOW()-2),(NOW()-1);<BR/>SET @x = 0;<BR/>insert into t1 SELECT FROM_UNIXTIME(@x:=@x+1) FROM t a,t b,t c,t d,t e,t f;<BR/>delete from t1 limit 10;Dirk1231https://www.blogger.com/profile/01604966231936807391noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-35939124786638042232007-12-17T20:41:00.000+01:002007-12-17T20:41:00.000+01:00create table t ( `id` mediumint unsigned );insert ...create table t ( `id` mediumint unsigned );<BR/><BR/>insert into t values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1);<BR/><BR/>SET @x = 0;<BR/><BR/>insert into t1<BR/>SELECT FROM_UNIXTIME(@x:=@x+1)<BR/>FROM t a,t b,t c,t d,t e,t f;Dirk1231https://www.blogger.com/profile/01604966231936807391noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-67008108430407580092007-12-17T20:31:00.000+01:002007-12-17T20:31:00.000+01:00Oops. Had a typo... the last line should have been...Oops. Had a typo... the last line should have been:<BR/><BR/>insert into t1 select from_days(@cnt:=@cnt+1) from t1,t1 b limit 990000;<BR/><BR/>-DipinAnonymoushttps://www.blogger.com/profile/17903278771915130754noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-82251519617999857042007-12-17T19:54:00.000+01:002007-12-17T19:54:00.000+01:00-- Puzzle: insert 1 million unique rows to the fol...-- Puzzle: insert 1 million unique rows to the following table<BR/>drop table if exists t1;<BR/>create table if not exists t1 (<BR/> dt datetime not null,<BR/> primary key (dt)<BR/>);<BR/><BR/>-- Five-line solution with lines =< 72 characters.<BR/>insert into t1 values (71201),(71202),(71203),(71204),(71205);<BR/>insert into t1 values (71206),(71207),(71208),(71209),(71210);<BR/>set @i=to_days('2007-08-22');<BR/>insert ignore into t1 select from_days(100+(@i:=@i+1)) from t1 a, t1 b;<BR/>insert ignore into t1 select from_days(@i:=@i+1) from t1 a,t1 b,t1 c;<BR/><BR/>-- Simpler three-line solution if lines can be up to 102 characters<BR/>insert into t1 values (71201),(71202),(71203),(71204),(71205),(71206),(71207),(71208),(71209),(71210);<BR/>set @i=to_days('2007-11-30');<BR/>insert ignore into t1 select from_days(@i:=@i+1) from t1 a,t1 b,t1 c,t1 d,t1 e,t1 f;<BR/><BR/>-- Fun puzzle! Bill KarwinBill Karwinhttps://www.blogger.com/profile/13004667086865377598noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-18918288380750558742007-12-17T19:38:00.000+01:002007-12-17T19:38:00.000+01:00insert into t1 values(from_days(366)),(from_days(3...insert into t1 values(from_days(366)),(from_days(367)),(from_days(368));<BR/>set @cnt = 369;<BR/>insert into t1 select from_days(@cnt:=@cnt+1) from t1,t1 b,t1 c,t1 d,t1 e;<BR/>insert into t1 select from_days(@cnt:=@cnt+1) from t1,t1 b,t1 c limit 9754;<BR/>insert into t1 select from_days(@cnt:=@cnt+1) from t1,t1 b limit 90000;Anonymoushttps://www.blogger.com/profile/17903278771915130754noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-72981475176168529292007-12-17T19:17:00.000+01:002007-12-17T19:17:00.000+01:00My version:DELIMITER $$CREATE FUNCTION `f`() RETUR...My version:<BR/><BR/>DELIMITER $$<BR/>CREATE FUNCTION `f`() RETURNS INT BEGIN<BR/>REPEAT INSERT INTO t1 SELECT @d:=DATE_ADD(@d,INTERVAL 1 SECOND);<BR/>SET @c=@c+1; UNTIL @c>100000 END REPEAT; RETURN 1; END$$<BR/>SET @c=1,@d=NOW()$$ SELECT f()$$Unknownhttps://www.blogger.com/profile/00409986569937662287noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-45034576225711837172007-12-17T18:16:00.000+01:002007-12-17T18:16:00.000+01:00I felt so close to a solution for a moment. I had ...I felt so close to a solution for a moment. I had that great idea about a 6-way cartesian join inserting bulks of SLEEP(1)+SYSDATE() into t1 which would only take a week and a half to complete! Too bad it was over the 75-chars limit... ;)Hubert Roksorhttps://www.blogger.com/profile/08860431047737411947noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-29265981431872905412007-12-17T18:07:00.000+01:002007-12-17T18:07:00.000+01:00another solution ;-)set @n:=1;insert t1 values (99...another solution ;-)<BR/><BR/>set @n:=1;<BR/>insert t1 values (990101),(990102),(990103),(990104),(990105),(990106);<BR/>insert t1 select 990109+interval @n:=@n+1 second from t1,t1 a,t1 b,t1 c;<BR/>insert t1 select 990109+interval @n:=@n+1 second from t1,t1 a limit 998698;zhurhttps://www.blogger.com/profile/02334841385147435107noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-75920520268133170422007-12-17T18:01:00.000+01:002007-12-17T18:01:00.000+01:00well,i have a rather boring brute-force solution:c...well,<BR/><BR/>i have a rather boring brute-force solution:<BR/><BR/>create table a select 1 id union select 2 id union select 3 id;<BR/>create table b (id int auto_increment primary key);<BR/>insert into b select 0 from a b,a c,a d,a e,a f,a g,a h;<BR/>insert into b select 0 from b a, b b limit 997813;<BR/>insert into t1 select from_unixtime(id) from b;<BR/><BR/>regards,<BR/>ephesAnonymoushttps://www.blogger.com/profile/06694809814028168950noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-32567197360950102312007-12-17T17:26:00.000+01:002007-12-17T17:26:00.000+01:00drop table if exists t1;create table t1 (dt dateti...drop table if exists t1;<BR/>create table t1 (<BR/>dt datetime not null,<BR/>primary key (dt)<BR/>);<BR/>SET @a=0;<BR/>INSERT INTO t1 VALUES (ADDDATE(NOW(),@a:=@a+1)),(ADDDATE(NOW(),@a:=@a+1));<BR/>INSERT INTO t1 SELECT ADDDATE(NOW(),@a:=@a+1) FROM t1 a, t1 b, t1 c, t1;<BR/>INSERT INTO t1 SELECT ADDDATE(NOW(),@a:=@a+1) FROM t1 a, t1 b, t1 c, t1;<BR/>INSERT INTO t1 SELECT ADDDATE(NOW(),@a:=@a+1) FROM t1 a, t1 LIMIT 895006;<BR/><BR/>- MorganMorgan Tockerhttps://www.blogger.com/profile/07820955267400574921noreply@blogger.comtag:blogger.com,1999:blog-16959946.post-33742744364414357112007-12-17T16:55:00.000+01:002007-12-17T16:55:00.000+01:00Mstar,the prize, as mentioned in the post, is "fam...Mstar,<BR/>the prize, as mentioned in the post, is "fame and fortune", i.e. just the glory. :)Giuseppe Maxiahttps://www.blogger.com/profile/15801583338057324813noreply@blogger.com