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:
select t1.city, t1.region, t1.latitude, t1.longitude from
hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1
Explain:
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
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 20029
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: locId
key: locId
key_len: 5
ref: helloneighbour_com_1.t1.locId
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
Table structure T1:
***************************
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:
***************************
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:
**************************
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:
**************************
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.
Cheers
James Riordon
Hosting | Webdesign | PHP | DTP
http://www.outofcontrol.ca
