0

I've seen here that MySQL does not re-index PRIMARY KEY's with AUTO_INCREMENT when records are deleted from the table. That is, if I have

1     John Connor     12/05/1973
2     Peter Brown     04/03/1979
.....
50    Mike Kennedy    01/12/1975

and I delete records starting at ID = 3 and ending at ID = 50, and then insert the following (in this exact same sequence, one after another):

      Mark Carney     09/02/1970
      Jimmy Johnson   14/09/1968
      Roman Harley    19/05/1972

I would end up with:

1     John Connor     12/05/1973
2     Peter Brown     04/03/1979
53    Roman Harley    19/05/1972
52    Jimmy Johnson   14/09/1968
51    Mark Carney     09/02/1970

When more records are inserted, the column ID increments just as expected (i.e. 54, 55, 56, ...), however the order of records becomes reversed. In the example above, 53 is inserted above 52, and 52 is above 51. Isn't the result supposed to be

1     John Connor     12/05/1973
2     Peter Brown     04/03/1979
51    Mark Carney     09/02/1970
52    Jimmy Johnson   14/09/1968
53    Roman Harley    19/05/1972

as our common sense would tell us? Why are the newer records placed on top of the older ones? If this has to do with the "table cells" (which were vacated once the records from ID = 3 to ID = 50 were deleted), then why doesn't 51 take place of 3, 52 place of 4, and 53 place of 5? Is there a way to change this behavior?

P.S. I am storing data with MySQL, querying with PHP, and managing the DB with phpMyAdmin. The PK in the table is set as follows: ID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY.

Thank you in advance!

Community
  • 1
  • 1
Alex
  • 3,719
  • 7
  • 35
  • 57
  • 2
    You aren't specifying any ordering in your query, therefore the database is free to return them in whatever order it feels like. – Willem Renzema Jul 24 '16 at 16:20
  • It's easier to ORDER BY in a query, however how do you ensure that the data is stored in the proper order internally, so as to avoid the mess? – Alex Jul 24 '16 at 16:23
  • 3
    There is no 'proper' order. Rows in a database represent unordered sets. This is their beauty. And dates should be stored using a date data type (if they're not already) – Strawberry Jul 24 '16 at 16:28
  • The autonumber field is only for linking records between tables or creating indexes. You don't use it for ordering records. You would put your own column in there for that - either a SortOrder column, or a DateEntered column, depending on the data. –  Jul 24 '16 at 16:28
  • Voting to close as a typo, cuz, why not – Drew Jul 24 '16 at 16:50
  • My point was to not use an auto-number field to do it. There are lots of ways to order records, but if he wants to depend on a field, he would have to create one. –  Jul 24 '16 at 16:58
  • @PhillipXT if you want sequential ordering, no better place than an AI to handle it for you. Unless you are into your own locking scheme – Drew Jul 24 '16 at 17:10
  • So if you have something like days of the week, you should use AI instead of adding a SortOrder to put them in the expected order? –  Jul 24 '16 at 17:11
  • @N.B. Can you please explain your understanding of the term "reindexing"? Thanks – Alex Jul 24 '16 at 17:14
  • My understanding of reindexing the primary key? Sure - it's the same as making the water wetter by adding water, making the photon travel faster by turning the light on while driving the car etc. - it makes **no sense**. – N.B. Jul 24 '16 at 18:51
  • @Alex To add to N.B.'s comment, see this about primary keys and indexes: http://stackoverflow.com/q/1071180/1797579 You use of "re-index" suggests you mean that you want the ordering to be changed. However, index already has a meaning in databases, and saying "re-index" the primary key is indeed like saying re-water water. – Willem Renzema Jul 24 '16 at 19:12

0 Answers0