IP number lookup in startIPNum and endIPNum range with Performance Optimization for Hibernate

I have table with ID, startIPNum and endIPNum. startIPNum and endIPNum are already converted long numbers not IP strings.

Following query works and takes somewhere between 3000 and 3200 ms

 List<GeoIP> oneResult = new LinkedList(getHibernateTemplate().find(
            "from GeoIP where "+ipNum+" >= startIpNum and "+ipNum+" <= endIpNum"));

This query works and takes somewhere between 3000 to 4700 ms

List<GeoIP> oneResult = new LinkedList(getHibernateTemplate().find(
            "from GeoIP where "+ipNum+" between startIpNum and endIpNum"));

The question is: is there a way to optimize this lookup to take a lot less time? The table has over 3'500'000 records.

Hard to say without knowing how you've indexed the table, but a combined index on startIpNum and endIpNum is probably needed:

CREATE INDEX range_idx ON geoip (endIPNum, startIPNum);

You may also have to alter your code, depending on how smart the query optimizer is. I did a similar lookup functionality where combined indices didn't work well, so I indexed only the range end instead and did something like this:

candidateRanges = query("from geoip where range_end >= $ip order by range_end")
if candidateRanges.size > 0 and candidateRanges[0].range_start <= ip:
    # We found a matching record, do something with it
else:
    # No match :(

The problem if you index the range start (for most DBMSs), is that the range will be traversed from the least element, and you're actually interested in the greatest element, so even this indexed search will become an O(n) operation.

I was having similar performance issues, then used the techniques found here: http://jcole.us/blog/archives/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis/

Made a huge difference - anywhere from 60x to 100x improvement. YMMV.

I would suggest that the optimization is probably going to be in the DB, not the hibernate query. Either add better indexes to your table, as gustafc suggested, or perhaps write a stored procedure. But I doubt that hibernate alone will get you better performance.

What I ended up doing is get the database file from maxmind and there is 1000% performance improvement. As gustafc sad it is optimized structure just for this purpose. Here is my spring integration:

<bean id="lookupService" class="com.maxmind.geoip.LookupService">
    <constructor-arg index="0" type="java.io.File" value="classpath:GeoLiteCity.dat"/>
    <constructor-arg index="1" type="java.lang.String" value="1"/>
</bean>

and service code:

    GeoIPLocation rtn = new GeoIPLocation();
    Location l = lookupService.getLocation(ipString);
    rtn.setCountry(l.countryName);
    rtn.setRegion(l.region);
    rtn.setCity(l.city);
    rtn.setPostalCode(l.postalCode);

The retreival time is with this code between 2 and 9 ms !!!