The most efficient way to query ip ranges in mysql

I have a geoencoding database with ranges of integers (ip addresses equivalent) in each row fromip(long) toip (long). the integers are created from ip addresses by php ip2long

I need to find the row in which a given ip address (converted to long) is within the range.

What would be the most efficient way to do it? (keys and query)

If I do (the naive so开发者_开发知识库lution) select * from ipranges where fromip <= givenip and toip >= givenip limit 1 and the key is fromip, toip. then for the case where the ip address is not in any given ranges the search goes through all the rows.

SOME MORE INFO:

explain select * from ipranges where ipfrom <= 2130706433 and ipto >= 2130706433 order by ipfrom Asc limit 1|

gives me 2.5M rows (total 3.6M in the table). The key is:

PRIMARY KEY (ipfrom,ipto)

that does not seem to be efficient at all. (the ip above is in none of the ranges)

---

Your query is fine, put an index on (fromip, toip) which will be a covering index for the query. The table won't have to be examined at all, only the sorted index gets searched, which is as fast as you can be.

The search will not actually go through all the rows. Not only will it go through none of the rows, only the index, but it won't examine every entry in the index either. The index is stored as a sorted tree, and only one path through that tree will have to be followed to determine that your IP is not in the table.