Professional Web Applications Themes

Generating interger enumeration using select statement - MySQL

I want to create a view that enumerates all integers. Using the view in the following way select value from all_integers where value > 110 and value < 115 should return these values 111 112 113 114 Does anyone has a good solution. I tried creating the view below but the performance was unaccaptable for large integer. create view all_integers as select digit as value from digits union select d1.digit + d2.digit*10 from digits d1, digits d2 union select d1.digit + d2.digit*10 + d3.digit*100 from digits d1, digits d2, digits d3 union select d1.digit + d2.digit*10 + d3.digit*100 + d4.digit*1000 ...

  1. #1

    Default Generating interger enumeration using select statement

    I want to create a view that enumerates all integers. Using the view in
    the following way

    select value
    from all_integers
    where value > 110 and value < 115

    should return these values
    111
    112
    113
    114

    Does anyone has a good solution.

    I tried creating the view below but the performance was unaccaptable
    for large integer.

    create view all_integers as
    select digit as value
    from digits
    union
    select d1.digit + d2.digit*10
    from digits d1, digits d2
    union
    select d1.digit + d2.digit*10 + d3.digit*100
    from digits d1, digits d2, digits d3
    union
    select d1.digit + d2.digit*10 + d3.digit*100 + d4.digit*1000
    from digits d1, digits d2, digits d3, digits d4
    order by value

    /Fredrik

    frebe73@gmail.com Guest

  2. #2

    Default Re: Generating interger enumeration using select statement

    [email]frebe73[/email] wrote:
    > I want to create a view that enumerates all integers.
    > select digit as value
    > from digits
    > union ...
    You don't need to do unions if your digits table includes zero.

    create view all_integers as
    select d1.digit + d2.digit*10 + d3.digit*100 + d4.digit*1000
    from digits d1, digits d2, digits d3, digits d4;

    The above should generate values from 0 to 9999. On my machine (Windows
    SP, Pentium 4, 1GB RAM, MySQL 5.0.21) it returned this result in 0.0613
    seconds. I added an index to digits.digit, but it made no significant
    difference.

    I don't think ORDER BY should be included in view definitions.

    I then tried "select * from all_integers where `value` between 110 and
    115" and it returned in 0.0053 seconds.

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Generating interger enumeration using select statement

    > > I want to create a view that enumerates all integers.
    >
    > > select digit as value
    > > from digits
    > > union ...
    >
    > You don't need to do unions if your digits table includes zero.
    Yes, I realized that too.
    > create view all_integers as
    > select d1.digit + d2.digit*10 + d3.digit*100 + d4.digit*1000
    > from digits d1, digits d2, digits d3, digits d4;
    >
    > The above should generate values from 0 to 9999. On my machine (Windows
    > SP, Pentium 4, 1GB RAM, MySQL 5.0.21) it returned this result in 0.0613
    > seconds. I added an index to digits.digit, but it made no significant
    > difference.
    4 digits is ok, but then I tried 10, the response time was too long.
    The best thing would be to find a solution that don't need to actually
    calculate all values.
    > I don't think ORDER BY should be included in view definitions.
    Ok.

    /Fredrik

    frebe73@gmail.com Guest

Similar Threads

  1. Question Generating dynamic names for select box.
    By deepali.bhosale in forum ColdFusion
    Replies: 0
    Last Post: September 21st, 02:34 PM
  2. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  3. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM
  4. SELECT statement
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 23
    Last Post: August 18th, 01:12 PM
  5. Getting column name in Select statement
    By Jane in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139