MySQL Forums
Forum List  »  Performance

Re: Wierd index behaviour
Posted by: Sergey Petrunya
Date: September 15, 2005 02:36AM

Are you really interested in running the query
"SELECT country_name FROM `IP2Country` WHERE ip_from <= a AND ip_to >= b"
for different values of a and b?

I assume you have a table that contains a list of disjoint IP ranges and you
need to quickly identify the range a given IP address is from (i.e. a==b),
Here is a [likely] faster way to do it:

Create and populate a table of range bounds.

CREATE TABLE ip2range(
ipbound INT,
is_range_start BOOL,
INDEX(ipbound)
);

Then run the query to locate the nearest left bound:

SELECT ipbound, start_or_end, range_id
FROM ip2range FORCE INDEX(ipbound)
WHERE ipbound < $ipaddr
ORDER BY ipbound DESC LIMIT 1;

If the query returns no rows or is_range_start==FALSE that means that
$ipaddr doesn't lay in any of the range.

Otherwise you have the range bound and can get the range using this [fast]
query:

SELECT * from IP2Country WHERE ip_from = $ipbound;

Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog

Options: ReplyQuote


Subject
Views
Written By
Posted
1962
September 14, 2005 11:35PM
1269
September 15, 2005 01:55AM
1267
September 15, 2005 02:11AM
Re: Wierd index behaviour
1329
September 15, 2005 02:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.