Hello,
I am running Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (i386) using readline 4.3 on a Dual Xeon 2.4Ghz RHEL4 box with 4GB of RAM.
I have a query that takes anywhere from .25 - .85 seconds to run. Following are the query and the related table structures I have currently set up and the output from 'explain'. After twiddling with this query for some time, i cannot seem to get it to run any faster and was curious if i am over looking something, or am i simply stuck with a slow query. The problem is that this query runs each time a user comes to our website, so the slowness tends to add up a little.
Query:
Code:
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1,hn_iprange as t2
where t1.locid=t2.locid
and (t2.startIpNum < '2720518136' and t2.endIpNum > '2720518136')
limit 1
Explain results:
Code:
mysql> explain select t1.city, t1.region, t1.latitude, t1.longitude
-> from hn_iplocation as t1,hn_iprange as t2
-> where t1.locid=t2.locid
-> and (t2.startIpNum < '2720518136' and t2.endIpNum > '2720518136')
-> limit 1;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+-------+-------------+
| 1 | SIMPLE | t2 | range | locId,iprange | iprange | 5 | NULL | 35378 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | helloneighbour_com_1.t2.locId | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+-------+-------------+
Table structure T1:
***************************
Code:
mysql> describe hn_iplocation;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| locId | int(16) unsigned | | PRI | NULL | auto_increment |
| country | char(2) | YES | | NULL | |
| region | char(2) | YES | | NULL | |
| city | varchar(45) | YES | MUL | NULL | |
| postalCode | varchar(7) | YES | MUL | NULL | |
| latitude | float(9,5) | YES | | NULL | |
| longitude | float(9,5) | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
Table structure T2:
***************************
Code:
mysql> describe hn_iprange;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| startIpNum | int(10) unsigned | YES | MUL | NULL | |
| endIpNum | int(10) unsigned | YES | | NULL | |
| locId | int(16) unsigned | YES | MUL | NULL | |
+------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Index from T1:
**************************
Code:
mysql> show index from hn_iplocation;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| hn_iplocation | 0 | PRIMARY | 1 | locId | A | 20029 | NULL | NULL | | BTREE | |
| hn_iplocation | 1 | postalcode | 1 | postalCode | A | 1820 | NULL | NULL | YES | BTREE | |
| hn_iplocation | 1 | city | 1 | city | A | 1820 | NULL | NULL | YES | BTREE | |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
Index from T2:
**************************
Code:
mysql> show index from hn_iprange;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| hn_iprange | 1 | locId | 1 | locId | A | 20587 | NULL | NULL | YES | BTREE | |
| hn_iprange | 1 | iprange | 1 | startIpNum | A | 82350 | NULL | NULL | YES | BTREE | |
| hn_iprange | 1 | iprange | 2 | endIpNum | A | 82350 | NULL | NULL | YES | BTREE | |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
T1 has 20,029 rows
T2 has 82350 rows
Many thanks to anyone who can offer any insight to this quandary.
dBforums
>
Slow query using Between and less than greater than
05-04-06, 20:06
Linear Mode
