Bug #11613 function to get lowest available id
Submitted: 28 Jun 2005 13:11 Modified: 11 Oct 2005 13:41
Reporter: Emil Obermayr Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version: OS:
Assigned to: Assigned Account CPU Architecture:Any

[28 Jun 2005 13:11] Emil Obermayr
Description:
I'd like to have a function that give the lowest available/free value in a numerical unique column. 

This function could replace this "bulky" statement:

select a.UID-1 as free  from user as a left join user as b on a.UID - 1 = 
b.UID where a.UID>500 && b.UID is null  order by a.UID limit 1;

This function could also be usefull if you have incident-IDs that have to be unique while in use, but 1.)  can be re-used after an incident is finally closed and  2.) should be is low/short as possible for easy human-readability

The function should have an optional parameter for the lowest allowed value, 500 in the example above.

I discussed this on the "Linuxtag Karlsruhe 2005" some days ago and it was recommended to report this here.

How to repeat:
(how to repeat a feature request... erm ;-) )

any time you need a small range of unique keys and don't want the values to grow to infinity

Suggested fix:
the function could be implemented like the sql-statement above, but it should be much faster if implemented direktly in the code

maybe its also/additionally possible to have an option for auto-increment not to use the next higher number, but let it find the lowest free one in a similar way to keep the used range of keys as small as possible in tables with lots of "traffic"
[30 Jun 2005 11:19] Geert Vanderkelen
Hallo Emil!

Sorry if I don't get your point there, but this does not help at al?

mysql> select min(i)-1 from uid;

Not sure who you talked to in Karlsruhe, but I know there was beer and cake! :)

Cheers,

Geert
[30 Jun 2005 11:33] Emil Obermayr
given to following values:

4
5
7
8

min()-1 would result in 3

which is correct as long as 3 is a valid value, but if you have a lowest allowed value, e.g. 1 with the following values:

1
2
3
5
6

min()-1 would result in 0 which is forbidden and "my" function would give 4 which is ok

So the point is to find the "holes" in the range of values with a given lower limit of allowed values. 

I forgot to note the name of the person I spoke with, lazy me :)  But I remember he was german, standing at the front desk on Friday and asked someone hacking on the tables behind.
[5 Jul 2005 13:27] Geert Vanderkelen
Hi Emil,

I digged some old query up I used for similar purpose, changed it a bit..

select @prev := min(i) from uid; select * from (select i, i-@prev as diff, i-(i-@prev)+1 as next, @prev:=i as prev from (select i from uid order by i asc) as u) as uu where diff > 1 limit 1;

This does what you think of, I'm not sure if this is actually useful to make a function about such a query. Ofcourse, that's not my call :)
These things are rather subjective: do you start from bottom up or filling holes from the top? Do you have date fields or simple int fields? Do you want to go in steps by 2 or 3?... Do you start at 1000 for normal UNIX users or 500 for system users, or or..

IMHO, since we have Stored Procedures and subqueries in 5.0 now.. you can implement such things easily on serverside, in any form you want.

What you think?

Cheers,
Geert
[7 Aug 2005 4:19] Ben Grabkowitz
I think this is a simpler method of getting what you want:

SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
       ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL
[11 Oct 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".