Generating interger enumeration using select statement - MySQL

[frebe73@gmail.com]

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

[Bill Karwin]

[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.

[frebe73@gmail.com]

> > 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