For a given list of ranges, let's say a bilion rows such as

id nra - indexed nrb
1 100 700
2 12,000,000 12,005,000
3 12,006,000 17,000,000
4 17,000,001 17,000,002

The ranges are never overlapping and there may be gaps like if you looked up number 800 the search should fail. But searching for 16,000,000 it should return row 3.

Now this is a design question.

I would do

SET @xxx = 106;
SELECT * FROM `test` WHERE @xxx >= nra AND @xxx <= nrb;

But is this the best way? What are faster ways to lookup and find the right row?

I am not too familiar with spatial features of MySQL but could a range from nra to nrb be considered a POLYLINE? Is MySQL faster in finding a XY coordinate is within a line or within an area?

Or maybe something other than a POLYLINE, a SET perhaps?

Or is MySQL a good choice? Is there another technology that is better for this kind of a lookup?