autoincrement...is this a problem

c1lonewolf · 12-18-2012, 06:37 PM

My datatable auto increments the id's. When I just added to new entries and checked datatble using phpmyadmin things were all out of whack!

Last page (7) id's read:
183,184,185,186,187

When I went looking for the new entries I found them on page 3 which read like:
77,78,79,188,189,81,82,83

Is this a problem or is mysql supposed to do that?

Old Pedant · 12-19-2012, 12:55 AM

I have no idea what you mean by "went looking for new entries" or, for that matter, what "page 3" is.

But here's a clue for you: If you do *NOT* specify an ORDER BY clause in a SQL SELECT, then MySQL (and *any* database) is allowed to return the records to you in ANY ORDER IT WANTS TO, including totally random order.

*MOST* databases will simply return the records to you in the order they are found on the disk. And you need to remember that databases *WILL* RE-USE space caused by deleted records.

If I had to guess, I'd say you deleted your record with auto_number value 80 and it was a big record (longer than average VARCHAR values??). So MySQL was able to fit *TWO* records (auto-number values 188 and 189) into the space opened up when you deleted 80.

But that is just a guess. Once again, THE ORDER OF AN AUTO_INCREMENT COLUMN MAY OR MAY NOT BE the order of the records on disk and MAY OR MAY NOT BE the order you get the records if you just do a SELECT without any ORDER BY.

If you want to see that latest records, by auto_increment value, the best thing to do is usually

Code:

SELECT * FROM yourtable ORDER BY auto_increment_field DESC LIMIT 20

or similar.

felgall · 12-19-2012, 01:14 AM

Quote:

Originally Posted by c1lonewolf

Is this a problem or is mysql supposed to do that?

It is definitely supposed to do that.

1. The records will be fit in as best as they can be in the actual file without regard to their key values.

2. All an auto-increment value represents is a unique value to identify each record. There is no particular reason why they have to be in order - it is just easier to generate them that way.

c1lonewolf · 12-21-2012, 05:06 PM

Thanks guys that's what I needed to know!