Good morning James -
It looks like you have a multi-column index on the startIpNum and
endIpNum columns, but it's not doing you any good, at least not for
this query.
You don't mention how many rows of data you're searching against,
which would give a better idea as to what might be reasonable to
expect for maximum speed of this query.
Part of the reason is that MySQL (at least prior to 5) will only use
one index per instance of a table per query, and it has chosen the
primary index from that "t2" table. Another part of the reason is
that MySQL can't compare a value to a value in an index that is not
at the beginning of said index.
A final reason you may experience a speed problem is that you've put
the number inside quotes, which makes it a string and could well be
forcing MySQL to do a datatype conversion on the data in your tables
prior to comparisons. Try your query without quotes first to see
what kind of difference that makes for you.
Otherwise -
Try joining on the second table again, and comparing against
startIpNum on that table. This makes your query more complex but
might allow MySQL to better use your existing indices. You could
take it a step further and add an index on endIpNum all by itself,
and add the table in a third time, also.
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1, hn_iprange as t2, hn_iprange as t3
where t1.locid=t2.locid
and t1.locid = t3.locid
and t2.locid = t3.locid
and (2720518136 between t3.startIpNum and t3.endIpNum)
limit 1
or for real fun, add an index to endIpNum and run:
select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1, hn_iprange as t2, hn_iprange as t3, hn_iprange as t4
where t1.locid=t2.locid
and t1.locid = t3.locid
and t1.locid = t4.locid
and t2.locid = t3.locid
and t2.locid = t4.locid
and t3.locid = t4.locid
and (2720518136 >= t3.startIpNum)
and (2720518136 <= t4.endIpNum)
limit 1
Hope this helps!
Dan
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
