Welcome to WebmasterWorld Guest from 54.162.155.183

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

any suggestion to make it more faster like less then a second?

     
5:03 am on Feb 5, 2013 (gmt 0)

5+ Year Member



Hi.. need help making my query faster... please help..

my table geoip_ip_blocks has 2M+ rows and geoip_locations has 290k+ rows...

here's my queries...


mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b, geoip_locations AS l WHERE b.locID = l.locID AND INET_ATON('113.114.118.94') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id | city | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 | 175.6167 |
+---------+------------------+----------+------------+
1 row in set (2.40 sec)

mysql>
mysql> SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b INNER JOIN geoip_locations AS l ON b.locID = l.locID WHERE INET_ATON('113.114.118.94') BETWEEN b.startIpNum AND b.endIpNum;
+---------+------------------+----------+------------+
| id | city | latitude | longtitude |
+---------+------------------+----------+------------+
| 1859525 | Palmerston North | -40.3500 | 175.6167 |
+---------+------------------+----------+------------+
1 row in set (1.58 sec)

mysql> explain SELECT b.id,l.city,l.latitude,l.longtitude FROM geoip_ip_blocks AS b INNER JOIN geoip_locations AS l ON b.locID = l.locID WHERE INET_ATON('113.114.118.94') BETWEEN b.startIpNum AND b.endIpNum;
+----+-------------+-------+--------+---------------------+---------+---------+------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+---------+---------+------------------+---------+-------------+
| 1 | SIMPLE | b | ALL | startIpNum,endIpNum | NULL | NULL | NULL | 2106604 | Using where |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 8 | vtracker.b.locID | 1 | |
+----+-------------+-------+--------+---------------------+---------+---------+------------------+---------+-------------+
2 rows in set (0.16 sec)

mysql>



any suggestion to make it faster?
9:21 am on Feb 5, 2013 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



i never use BETWEEN
i always use: WHERE x>=y AND x<=z

also in my IP database i've converted the ip address to a number (there's a formula for doing this) lookups are quicker that way

do you have any indexes on the tables?
9:58 am on Feb 5, 2013 (gmt 0)

WebmasterWorld Administrator incredibill is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



@topr8 the INET_ATON() basically converts the IP into a 32-bit long.
1:48 pm on Feb 5, 2013 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



The tricky part of the query looks like the one with on the geoip_ip_blocks as the where clause with 2 constraints means you're touching on 2106604 entries.

I'd take the complexity of the join out of it to start with: just try to optimize the query on the block id. You can then later on work on the join.

I'd try to see what effect creating indexes on the start and end IP addresses has (to try to get the database not to search all lines)...
2:01 pm on Feb 5, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



i never use BETWEEN
i always use: WHERE x>=y AND x<=z


BETWEEN is translated to x>=y AND x<=z anyways, so it makes no difference speed wise. I agree that it's better to not use BETWEEn given you get a better level of control with no speed costs.
11:40 am on Feb 12, 2013 (gmt 0)



Try to switch conditions in the WHERE clause. The most restrictive condition should go first, because the second is then operating on less number of rows.

If I understand it correctly, then second part of WHERE clause should return just few rows, for which you then match blocks to locations.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month