Comments on: Generating numbers out of seemingly thin air https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air Blog by Shlomi Noach Wed, 02 Sep 2009 03:38:45 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: shlomi https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3641 Wed, 02 Sep 2009 03:38:45 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3641 @adj4096

Would you care to elaborate?
If so, you can use the INFORMATION_SCHEMA tables.

]]> By: ajd4096 https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3637 Tue, 01 Sep 2009 23:02:59 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3637 Don’t be so sure that the help tables are *always* installed.

]]> By: Rob Wultsch https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3631 Tue, 01 Sep 2009 15:24:18 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3631 A much cleaner way of doing what is described above can be found in generate_series ( http://www.postgresql.org/docs/8.4/static/functions-srf.html ) which is found in a different free RDMS…

]]> By: Toby https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3630 Tue, 01 Sep 2009 13:18:23 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3630 strcmp, It can be phrased as a self join, but may not be efficient (even quadratic): https://slashdot.org/~toby/journal/199210

]]> By: strcmp https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3629 Tue, 01 Sep 2009 12:52:39 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3629 as a former assembler and C programmer and number cruncher iterative code like “@counter := @counter + 1” looks suboptimal to me, because it is a data dependency between the iterations, forcing the code to be executed serially. of course that’s just me and a totally useless comment right now, but even MySQL may once leave the stone ages and execute JOINs and table/index scans in parallel… bulk operations ‘feel’ better.

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3625 Tue, 01 Sep 2009 11:15:56 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3625 @Giuseppe,
Thanks for the references; Very interesting!

@Mark,
What a coincidence!

@strcmp,
My personal view is that shorter is usually better. Your solution has the property of choosing exactly 1000 thousand values, though.

]]> By: strcmp https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3619 Tue, 01 Sep 2009 09:14:47 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3619 I mostly use UNIONs for this purpose:

SELECT a.x*100+b.x*10+c.x
FROM (
SELECT 0 x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) a, (
SELECT 0 x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) b, (
SELECT 0 x UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) c

I don’t know what’s better. You don’t need more priviledges for this than SELECT and it works with 4.1, but of course that puts more load onto the parser and you have much more UNIONs. I’m hoping that generating the bulk of the data with JOINs uses optimized code in the MySQL server like the JOIN buffer and temporary tables and that the overhead doesn’t matter any more.

]]> By: Mark https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3618 Tue, 01 Sep 2009 08:03:16 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3618 Interesting stuff about the help_topic table, thanks. I’d just worked out how to calculate the Fibonacci sequence on my blog (using one of my defined tables to join against) as part of my investigation into solving problems on the Euler project. http://www.oxfordtechnotes.co.uk/sqlblog/blog4.php/2009/08/29/project-euler-q2-with-mysql I may well use the help_topic table in future.

]]> By: Giuseppe Maxia https://shlomi-noach.github.io/blog/mysql/generating-numbers-out-of-seemingly-thin-air/comment-page-1#comment-3617 Tue, 01 Sep 2009 07:54:06 +0000 https://shlomi-noach.github.io/blog/?p=1148#comment-3617 On the same vein, you may like these two posts:

http://datacharmer.blogspot.com/2007/12/data-from-nothing-solution-to-pop-quiz.html
http://datacharmer.blogspot.com/2007/12/pop-quiz-with-prize-generate-4-billion.html

Giuseppe

]]>