How do I find the row where a certain value is between 2 others?

Question

So, I have a mysql table that looks roughly like this, where the first 2 columns contain ranges.

1 , 5  , Value1
6 , 14 , Value2
14 , 18 , Value3

How can I query to find which row a certain value falls between (ie, if I have 9, it returns Value2; if I have 2, it returns Value1). This table is about 10mb big, so the more efficient, the better.

Thanks!

Answer 1

where fielda >= 9 and fieldb <=  9 

add a index that contain this 2 field on your table

Answer 2

SELECT
  val
FROM
  table
WHERE
  table.range_min <= 9
  AND table.range_max >= 9
  /* OR 9 BETWEEN table.range_min AND table.range_max*/

For efficiency add an index for range_min and range_max column.

Answer 3

select col3 from thetable where 2 between col1 and col2

You'd like a combined index on col1 and col2 for this to perform well.

Answer 4

Actually it's almost the same as madgnome's answer, however IMO a bit cleaner:

SELECT ... WHERE 9 BETWEEN min_value AND max_value;