CodingForums.com - View Single Post

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.

12-19-2012, 12:55 AM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,200 Thanks: 59 Thanked 3,996 Times in 3,965 Posts	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. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.