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

[camilord]

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]

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]

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

[swa66]

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]

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]

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.