title: Is a MySQL primary key already in some sort of default order
body: <p>I just stumbled upon a few lines of code in a system I just started working with that I don't really get. The system has a large table that saves lots of entities with unique IDs and removes them once they're not longer needed but it never reuses them. So the table looks like this </p>
  
  <pre><code>------------------------
  | id |info1|info2|info3|
  ------------------------
  | 1  | foo1| foo2| foo3|
  ------------------------
  | 17 | bar1| bar2| bar3|
  ------------------------
  | 26 | bam1| bam2| bam3|
  ------------------------
  | 328| baz1| baz2| baz3|
  ------------------------
  etc.
  </code></pre>
  
  <p>In one place in the codebase there is a while loop whose purpose it is to loop through all entities in the DB and do things to them and right now this is solved like this</p>
  
  <pre><code>int lastId = fetchMaxId()
  int id = 0
  while (id = fetchNextId()){
    doStuffWith(id)
  }
  </code></pre>
  
  <p>where fetchMaxId is straight forward</p>
  
  <pre><code>int fetchMaxId(){
    return sqlQuery("SELECT MAX(id) FROM Table")
  }
  </code></pre>
  
  <p>but fetchNextId confuses me. It is implemented as</p>
  
  <pre><code>int fetchNextId(currentId, maxId){
    return sqlQuery("
      SELECT id FROM Table where id &gt; :currentId and id &lt;= :maxId LIMIT 1
    ")
  }
  </code></pre>
  
  <p>This system has been in production for several years so it obviously works but when I tried searching for a solution to why this works I only found people saying the same thing that I already thought i knew. The order in which a MySQL DB returns the result is not easily determined and should not be relied upon so if you wan't a particular order use a ORDER BY clause. But are there some times you can safely ignore the ORDER BY? This code has worked for 12 years and continued to work through several DB updates. Are we just lucky or am I missing something here? Before I saw this code I would have said that if you called </p>
  
  <pre><code>fetchNextId(1, 328) 
  </code></pre>
  
  <p>you could end up with either 17 or 26 as the answer. </p>
  
  <p>Some clues to why this works may be that the id column is the primary key of the Table in question and it's set to auto increment but I can't find any documentation that would explain why </p>
  
  <pre><code>fetchNextId(1, 328)
  </code></pre>
  
  <p>should always returns 17 when called on the table-snippet given above.</p>