Hi there!

my_table has 23043 records.
The next autoindex indicates 23047.
I have verified the largest index value allocated which is 23046 and which is correct.

How can I find the two index values which aren't used?
(so I can use them with new records and therefore have the index values and the number of records having the same value : 23046).

Many thanks in advance for your help.

please consider changing your strategy

the purpose of the auto_increment is to provide uniqueness only

any attempt to compensate for "gaps" in the numbers is wrong and you should try to find another way to do whatever it is you're doing with the numbers

agree with Rudi
autonumber columns / identity columns are system generated.. they have no meaning outside the system itself. if you are trying to force an exteranl meaning onto such a column it will fail.
if you need to have sequential numbers then you need to develop a stratgey to generate the nnext availabel number.. its been covered inthis trhead, and other trheads on this forum ad nauseam.

however to answer your question....
first off are you not bothered about hte third missing number in your set?
you could send a series of sql queries counting the number fo records in each set untill you find the missing gaps

the SQL would be something like select count(<mycolumn>) as NoRecs from <mytable> where <myautonumbercolumn> >x and <myautonumbercolumn> < y
you could do it by writign a bit of quick & dirty code or do it in something like MySQL Query Browser. if you satrted off say with a range of a hundred it woul failry quickly narrow down where the problem is, then make the range say 10's.. equally ytou can size a query window so that the display is in a simple multiple (Id suggest soemthign like displaying 20..40 records.. do a page down untill the last record on the screen is no longer in the sequence you expect. it may be quicker that say writing a bit a of code

headlem yuo nede a nwe kyeboadr, yuors kepes swtiching lettesr aruond

Nah, the keyboard is Ok, it is just a loose nut attached to the keyboard combined with a need for either a spell checker or a proof reader!

-PatP

This query will cough up the missing values (and the next in line)

Good luck.

Gr,
Yveau

faircopyerhonner.....

I think that the proble is getting worse.. mebbe Mad Cow Disease is more prevalent than I first thought