Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Query Optimisation - whera a >= 123 and b <= 123

    I need help on optimizing following query :


    SELECT * from location where code1 >= 520005 and code2 <=520005;

    This table is having approximate 1500000 rows. I tried using various combination of indexes but mysql is not using any index. This query takes between 3-8 seconds. Our application usage this query very very frequently. Any other alternate suggestion to speed up this query work

    Thanks in advance for all help.

    ebourn

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Query Optimisation - whera a >= 123 and b <= 123

    Originally posted by ebourn
    I need help on optimizing following query :


    SELECT * from location where code1 >= 520005 and code2 <=520005;

    This table is having approximate 1500000 rows. I tried using various combination of indexes but mysql is not using any index. This query takes between 3-8 seconds. Our application usage this query very very frequently. Any other alternate suggestion to speed up this query work

    Thanks in advance for all help.

    ebourn
    Can you post the EXPLAIN of that query as well as a DESCRIBE of your table and a SHOW INDEX for that table? That would help us to pinpoint the problem...

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    Hi bstjean,

    Thanks.

    Here is the actual query :

    SELECT * FROM i2l.ip2loc WHERE ip_from <= 1141533096 AND ip_to >= 1141533096

    it is taking time :

    Showing rows 0 - 0 (1 total, Query took 4.6680 sec)

    This is the explain of query :

    table type possible_keys key key_len ref rows Extra
    ip2loc ALL PRIMARY,ip_from_2 NULL NULL NULL 1248226 where used

    This is the table Structure :

    ip_from bigint(20) No
    ip_to bigint(20) No
    country_code char(2) Yes NULL
    country_name varchar(64) Yes NULL
    region varchar(128) Yes NULL
    city varchar(128) Yes NULL


    The following is the indexes

    Keyname Type Cardinality Field
    ===============================
    PRIMARY PRIMARY 1248226 ip_from
    ip_from_2 INDEX 1248226 ip_from ip_to




    once again thanks for your time.

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by ebourn
    Hi bstjean,

    Thanks.

    Here is the actual query :

    SELECT * FROM i2l.ip2loc WHERE ip_from <= 1141533096 AND ip_to >= 1141533096

    it is taking time :

    Showing rows 0 - 0 (1 total, Query took 4.6680 sec)

    This is the explain of query :

    table type possible_keys key key_len ref rows Extra
    ip2loc ALL PRIMARY,ip_from_2 NULL NULL NULL 1248226 where used

    This is the table Structure :

    ip_from bigint(20) No
    ip_to bigint(20) No
    country_code char(2) Yes NULL
    country_name varchar(64) Yes NULL
    region varchar(128) Yes NULL
    city varchar(128) Yes NULL


    The following is the indexes

    Keyname Type Cardinality Field
    ===============================
    PRIMARY PRIMARY 1248226 ip_from
    ip_from_2 INDEX 1248226 ip_from ip_to




    once again thanks for your time.
    Well, it looks like MySQL won't use the index because your query is probably returning more than 30% of the rows of that table... This is not a SQL problem or a MYSQL problem, any RDBMS would act the same way... Have you tried a with self join and adding an index on ip_to ? That's the only other shot you've got at this problem I guess...

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Hi bstjean,

    This query is returning only one row. I tried an index on ip_to also but does not work. In fact mysql is not using index in this query.
    Mysql Explain see possible keys (PRIMARY,ip_from) but mysql is not using.

    table type possible_keys key key_len ref rows Extra
    ip2loc ALL PRIMARY,ip_from_2 NULL NULL NULL 1248226 where used


    I tried some forced option like use index also in query but does not work.

    Can you give me any idea about self join query. Some sample query if possible.

    Thanks and regards

Posting Permissions

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