One of my MySQL tables has an ID column - it's a primary key with auto_increment. And it works. If I add six rows they number 1 to 6 nicely. But if I delete rows 4 to 6 then add a new row, it becomes row 7. And if I delete that it becomes row 8. How can I make it so the next auto_increment is the smallest possible? (i.e. 1,2,3,4,5,6 - I delete 4-6 and the next row inserted becomes 4)

Simply you can't.


Unchecking auto_increment flag you can do something like this:

INSERT INTO table
SELECT
max(id)+1 as id,
value1 as v1,
value2 as v2,
...,
valuen as vn
from table

Supposing you have a table with n+1 fields: ID,v1,...,vn

There is no automatic option for this. You can do this manually by going into the MySQL database operations and changing the AUTO_INCREMENT value or primary key (something similar like that) to the lowest possible non-occupied id number.
I don't know if it will skip over occupied numbers or just go back to using the highest number when it encounters a number that is already occupied (e.g. you already have a 7, but you don't have a 5, so you set it at 5, what happens when it gets to 7?)

But this is the only way to do this efficiently.

The simplest way to deal with this would be to select the entry with the highest ID (SELECT id FROM table ORDER BY id DESC LIMIT 1), and set the current auto_increment value to that ID number plus one. However, I am not sure whether this is possible, but common sense tells me it should be

I don't think that there really is a way to do that effeciently, you would have to do it manually, and it would probably require several calls to the database.

Why do you need this anyhow? Is it really all that important?

As the others pointed out, it's quite a hassle and not that feasible.
It shouldn't be more troublesome to rewrite whatever you're working with to work with the auto_increment and missing ID's. I assume you query the values with ID+1 - either do nullcheck or take the next value in list like all database programs do.

Its not feasible to do the AUTO_INCREMENT by the database if some records got delete. It needs to be done manually, as all other members have mention. And if this procedure is not that important to maintain the seq of the numbers, then just use what the database does.

Another point is that if you want to reset everything to start from 1, 2, .... etc again. By deleting all the records and insert new records again. The seq will be out. It will be last inserted.

Eg.
insert 1, 2, 3, 4
delete 1, 2, 3, 4

when you insert again. It will be 5, 6, 7, 8 and not 1, 2, 3, 4. So if you want to do it again starting from 1, use the ALTER function and alter the table. It will start all over again for the seq.

Was trying to get it to start the seq of number again and took awhile to figure that out. So just share some points, in case someone is like me, figuring that out got hrs.

Cheers

I dont think you can do that but I think it may be possible if you use php. Unselect auto increment and run a while. Get the ID and check it based on the last ID (find if it is +1 of last variable) then run an if tag. If it is +1 of the last var then do nothing but if not simply add the entrie. This works good for forums ect. so if member 5 is deleated. You allow easy to remember urls to re-appear (example: ID=5 is easyier then ID=156123). Get what I mean. But if you do it this way the ID can no longer be used as a way to order entries by when it was created. Here is an example of php you might use.
Now I haven't tested the code above. But it should work. This is the only way I could think of to solve your problem. Note: This assumes that your first ID is 1 not 0. If your first ID is 0 you need to add:after: Hopefully this will solve your problem,
Sparkx

Well, usually a lot of new users which goes into databases doesn't like that when you delete a record the auto increment continues from the last one and they want the database to be sorted, in fact it is a bad habit, the auto increment shouldn't be ever changed and every row should be unique, if some database guy would find out that you use something like that in your job, he would definitely say something, I don't think it is bad for personal use, but there is no point, it really doesn't matter how the data is stored, the main thing it is stored and with any language you can process it and sort it, select what you want, some people doesn't even use delete in their query, they just deletes the records, but leaves the row and just doesn't process empty rows and in my opinion this is quite good, even better would be to have a column of status and for example 0 would be hidden, 1 would be not hidden, something like that..

Of course, a lot of whom usually play with small databases, these kind of things isn't relevant, but it is a bad habit, I used to do such "hacks" myself, the only purpose for it, that the data would look more "beautiful" on phpMyAdmin

That's not true, one common use of AUTO_INCREMENT is to give unique numbers to members of a group (like forums). While it does not necessarily matter if users are ordered in sequence, there are some functionality things that can be made faster if the assumption that there are no gaps to worry about hold. The reason to keep no gaps in numbers for entries isn't from the SQL perspective, but from the integrating other things with SQL perspective.
That can be problematic if you have a large number of entries that are no longer relevant because some hosts and servers (such as, oh, for example, Astahost) place limits on the amount of space you have, and depending on what you use your databases for, they can grow very large very quickly.

~Viz

P.S. try using periods as well as commas in your posts to make things easier to read. Just some more helpful advice we all (including me) should follow to make better posts.