I have to agree with Anthony - why are you using row order to determine
something relating to users? I couldn't follow your brief explanation
above, and the fact that you're doing it sets off some soft alarm bells
about the design of your application. Why is it important that there
shouldn't be any 'gaps' between users? Because you want to know how many
users there are? If so, simply do a SELECT COUNT(*) on the table
whenever / wherever you need to know.
If you're using it for IDs for the users, it's generally a bad idea to
reuse this type of information. If you have some other purpose, I'm
extremely curious about what it might be.
What I was getting at is you get the unique id for the username (if
you allow username changes, then you want a unique key to do your
joins on from other tables).
Yep, that's one good reason among many for using unique ids. Thinking a little about the OP's question, I could understand row order being relevant in certain situations where you wanted to display something like, "You were the 432nd person to register at our site!", etc.
But, too often I've seen people new to database design not liking 'gaps' because 'user1' will have a unique id of '1', while 'user2' will have a unique id of '6' because the records associated with unique ids '2' through '5' were deleted during testing, and so on. So, they feel that 'user2' should have a unique id of '2', ignoring the fact that that's not a unique id at all, if you had id '2' associated with another record at some point.
I'm not suggesting this is what the OP is doing, just that that's why I was curious about the purpose.
Much warmth,
planetthoughtful
---
"Lost in thought"
http://www.planetthoughtful.org