title: Get next and previous records in an ordered list
body: <p>I'm currently using PHP to parse a sorted list of records and get the previous and next record of a given record. My DB has grown a lot and the query is taking too much time. </p>
  
  <p>I'd like to change the query so that it only returns the previous and next records and also takes less time. Maybe I need to add an index. </p>
  
  <p>Here's the current query : </p>
  
  <pre><code>SELECT id 
    FROM events 
   WHERE latitude BETWEEN 48.772559580567 AND 48.952423619433 
     AND longitude BETWEEN 2.2154977888952 AND 2.4889022111048 
   ORDER 
      BY start_time ASC
       , id
  </code></pre>
  
  <p>I have to sort by start_time &amp; id since several events can start at the same time. I have to use a sort so that the list is constant and next &amp; prev buttons always use the same algorithm. </p>
  
  <p>This currently takes between 0.1 and 0.4 seconds which is way too long. And then I need to parse in PHP the 6000 returned rows to get the next and previous records. </p>
  
  <p>I've read many similar topics but none of them seemed to address speed. Mostly I've found "min(id) FROM ... WHERE recordid > targetid UNION max(id) FROM ... WHERE recordid &lt; targetid" which means doubling the 0.1 to 0.4 seconds. </p>
  
  <p>Schema of the DB : </p>
  
  <ul>
  <li>id int(11) </li>
  <li>latitude double</li>
  <li>longitude double</li>
  <li>start_time timestamp</li>
  </ul>
  
  <p>indexes : </p>
  
  <ul>
  <li>id PRIMARY</li>
  <li>latitude</li>
  <li>longitude</li>
  <li>latitude+longitude</li>
  </ul>