title: Mysql performance between-and vs limit
body: <p>I'm wondering about potential performance problems on a query I'm writing.  There are more than enough posts about between/and sometimes being slow, and also limit being slow.  The column I need to reference always has sequential numbers in it, so it is possible for me to use either style.</p>
  
  <p>my table:</p>
  
  <pre><code>pkey INT, idcol INT, rank INT, datafields....
  </code></pre>
  
  <p>There is an index on (idcol, rank).  Rank is always sequential starting from 0.  It is not possible for there to be a gap in it (or else something is broken).  Thus, I have the following 2 options to get data:</p>
  
  <p>Option 1:</p>
  
  <pre><code>SELECT * FROM tbl WHERE idcol = 1 AND rank BETWEEN 12 AND 15 ORDER BY rank;
  </code></pre>
  
  <p>Option 2:</p>
  
  <pre><code>SELECT * FROM tbl WHERE idcol = 1 AND rank &gt;= 12 ORDER BY rank LIMIT 4;
  </code></pre>
  
  <p>I would guess option 1 is best but I'm not sure ... is it faster to just find '12' in the index and then get the next 3 rows without having to keep comparing rank &lt;= 15?</p>