title: How can I query between two columns while still taking advantage of indexes?
body: <p>Imagine I have a table contains all the chapters of a book and the start/end page from each chapter.</p>
  
  <pre><code>chapter |   start_page     | end_page
  --------------------------------------
     1    |        1         |    24
     2    |        25        |    67
     3    |        68        |    123
     4    |        124       |    244
     5    |        245       |    323
  </code></pre>
  
  <p>I'm attempting to find out what chapter a random page falls on, let's say page 215 for example.</p>
  
  <p>My first idea was to use a query like this</p>
  
  <pre><code>SELECT `chapter`
  FROM `book`
  WHERE `start_page` &lt;= 215
  AND `end_page` &gt;= 215
  </code></pre>
  
  <p>Unfortunately MySQL can not take advantage of indexes in the above query which is a large problem due to the large size of my table.</p>
  
  <p>After doing some research I came up with this query which does take advantage of indexes.</p>
  
  <pre><code>SELECT `chapter`
  FROM `book`
  WHERE `start_page` &lt;= 215
  ORDER BY `start_page` DESC     
  LIMIT 1
  </code></pre>
  
  <p>The issue now is I want the ability to query multiple random pages while still taking advantage of indexes. It doesn't seem likely that I can modify my last query since it's so heavily reliant on limiting results to one.</p>
  
  <p>Any advice would be much appreciated!</p>
  
  <p><strong>UPDATE:</strong> 
  Thanks to a comment from Ray Toal I have a query which gives me the results I need with amazing performance. </p>
  
  <pre><code>SELECT chapter 
  FROM book 
  WHERE (start_page = (SELECT max(start_page) FROM book WHERE start_page &lt;= 73) AND end_page &gt;= 73) 
  OR (start_page = (SELECT max(start_page) FROM book WHERE start_page &lt;= 92) AND end_page &gt;= 92) 
  OR (start_page = (SELECT max(start_page) FROM book WHERE start_page &lt;= 300) AND end_page &gt;= 300)
  </code></pre>