title: Optimizing ORDER BY LIMIT queries in MySQL
body: <p>In my web app I made an internal messaging system. I want to place a 'previous' and a 'next' link on each page (where the user viewing the message).</p>
  
  <p>In order to get the next and previous id I execute two queries:</p>
  
  <p>For the previous one:</p>
  
  <p><code>SELECT id FROM pages WHERE (id&lt;$requestedPageId) ORDER BY id DESC LIMIT 1</code></p>
  
  <p>And for the next one:</p>
  
  <p><code>SELECT id FROM pages WHERE (id&gt;$requestedPageId) ORDER BY id LIMIT 1</code></p>
  
  <p>EXPLAIN says the the query type is "range" and the rows column says it would examine all rows that has smaller or bigger id than the page's id (a big number). The Extra row says "Using where".</p>
  
  <p>It seems MySQL ignores that I want only one row. Isn't MySQL smart enough to optimize this kind of query so it would find the row for the page and search for the first matching row back/forth?</p>
  
  <p>Is there a better approach to get the next and previous page's id?</p>
  
  <p>Additional notes:</p>
  
  <ul>
  <li>This problem seems to exist on every ORDER BY LIMIT type queries (eg.: when I split a long list to multiple pages.).</li>
  <li>Where clause is not this simple (I want to let the user access the next/previous page he has permission to access. No joins though.)</li>
  <li>All columns appear in WHERE are indexed (id is the primary key)</li>
  <li>variables are protected against injection.</li>
  </ul>
  
  <p>EDIT1:</p>
  
  <p>So the query I'm using currently:</p>
  
  <pre><code>SELECT id
  FROM reports
  WHERE (id&lt;$requestedPageId) AND ((isPublic=1) OR (recipientId=$recipient))
  ORDER BY id DESC
  LIMIT 1
  </code></pre>
  
  <p>Or when I re-factor it as the answer said:</p>
  
  <pre><code>SELECT MAX(id)
  FROM reports
  WHERE (id&lt;$requestedPageId) AND ((isPublic=1) OR (recipientId=$recipient))
  </code></pre>