title: How can I optimize this range-based query?
body: <p>I have a MySQL database with four columns: <code>library_name</code>, a string, <code>function_name</code>, a string, <code>low_num</code>, a number, and <code>high_num</code>, a number. <code>low_num</code> and <code>high_num</code> together represent a range, and all the ranges belonging to the same library (having the same <code>library_name</code>) are mutually exclusive. I need to run support a query where, given a ~14,000 <code>str</code>-<code>num</code> pairs, for each pair it returns the row where <code>low_num &lt;= num &lt; high_num and library_name = str</code>. I've made my primary key <code>(library_name, low_num)</code>, and my current fastest query is <code>(select * from table where library_name = $name and $num between low_num and high_num limit 1) union (select ...) ...</code>. I.e., each pair gets its own query, and then they all get unioned together. However, it's still quite slow (takes around 20 seconds). I also run into memory issues when I do 1 query like this for the 14,000 pairs, so I'm having to break it up into ~14 queries to find 1,000 pairs each (but even one of these 1,000 pair queries on its own takes like 4 seconds). Any ideas how to speed up this query?</p>
  <p><code>SHOW CREATE TABLE</code>: <a href="https://db-fiddle.com/f/bjB1zLez2suhdCzt6itge5/0" rel="nofollow noreferrer">https://db-fiddle.com/f/bjB1zLez2suhdCzt6itge5/0</a></p>
  <p><code>EXPLAIN SELECT</code> (for just one of the selects in the union): <a href="https://db-fiddle.com/f/hyq7aKN89soLZDPyxyJu5T/0" rel="nofollow noreferrer">https://db-fiddle.com/f/hyq7aKN89soLZDPyxyJu5T/0</a></p>