Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    2

    Unanswered: Slow query using Between and less than greater than

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Quote Originally Posted by plattapuss
    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;
    startIpNum and endIpNum are defined as integers but your WHERE clause compares them against strings. You should have instead:

    (t2.startIpNum < 2720518136 and t2.endIpNum > 2720518136)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •