title: MySQL+PHP: How to paginate data from complex query with ORDER BY on user-selected column
body: <p>I have a table with currently ~1500 rows which is expected to grow over time (can't say how much, but still), the website is read-only and lets users do complex queries through the use of some forms, then the search query is completely URL-encoded since it's a public database. It's important to know that <strong>users can select what column data must be sorted by</strong>.</p>
  
  <p>I'm not concerned about putting some indexes and slowing down INSERTs and UPDATEs (just performed occasionally by admins) since it's basically heavy-reading, but I need to <strong>paginate</strong> results as some popular queries can return 900+ results and that takes up too much space and RAM on client-side (results are further processed to create a quite rich &lt;div&gt; HTML element with an &lt;img&gt; for each result, btw).</p>
  
  <ul>
  <li>I'm aware of the use of <code>OFFSET {$m} LIMIT {$n}</code> but would like to avoid it</li>
  <li>I'm aware of the use of this</li>
  </ul>
  
  <p>Query</p>
  
  <pre><code>SELECT *
  FROM table
  WHERE {$filters} AND id &gt; {$last_id}
  ORDER BY id ASC
  LIMIT {$results_per_page}
  </code></pre>
  
  <p>and that's what I'd like to use, but that requires rows to be sorted only by their ID!</p>
  
  <ul>
  <li>I've come up with (what I think is) a very similar query to custom sort results and allow efficient pagination.</li>
  </ul>
  
  <p>Query:</p>
  
  <pre><code>SELECT *
  FROM table
  WHERE {$filters} AND {$column_id} &gt; {$last_column_id}
  ORDER BY {$column} ASC
  LIMIT {$results_per_page}
  </code></pre>
  
  <p>but that unfortunately requires to have a {$last_column_id} value to pass between pages!</p>
  
  <p>I know indexes (especially unique indexes) are basically automatically-updated integer-based columns that "rank" a table by values of a column (be it integer, varchar etc.), but I really don't know how to make MySQL return the needed $last_column_id for that query to work!</p>
  
  <p>The only thing I can come up with is to put an additional "XYZ_id" integer column next to every "XYZ" column users can sort results by, then update values periodically through some scripts, but is it the only way to make it work? Please help.</p>