homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

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




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

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?

 

topr8




msg:4542473
 9:21 am on Feb 5, 2013 (gmt 0)

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?

incrediBILL




msg:4542480
 9:58 am on Feb 5, 2013 (gmt 0)

@topr8 the INET_ATON() basically converts the IP into a 32-bit long.

swa66




msg:4542531
 1:48 pm on Feb 5, 2013 (gmt 0)

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)...

StoutFiles




msg:4542534
 2:01 pm on Feb 5, 2013 (gmt 0)

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.

MinosTheNinth




msg:4544886
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved