Generate an integer sequence in MySQL
The following will return 1..10000 and is not so slow
SELECT @row := @row + 1 AS row FROM
(select 0 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) t,
(select 0 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) t2,
(select 0 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) t3,
(select 0 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) t4,
(SELECT @row:=0) numbers;
I found this solution on the web
SET @row := 0;
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r
Single query, fast, and does exactly what I wanted: now I can "number" the "selections" found from a complex query with unique numbers starting at 1 and incrementing once for each row in the result.
I think this will also work for the issue listed above: adjust the initial starting value for @row
and add a limit clause to set the maximum.
BTW: I think that the "r" is not really needed.
ddsp