GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Query Speed Help

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Speed Help

    Hi all,

    I've been breaking my head with this for a while now. I have a query, which is very slow and I'm trying to find a way to speed it up. It takes several seconds (usually between 3 and 10) to run the query.

    ----------------------------------------------------

    TABLE STRUCTURE:

    CREATE TABLE `ipcountry` (
    `ipFROM` double unsigned NOT NULL default '0',
    `ipTO` double unsigned NOT NULL default '0',
    `countrySHORT` char(2) NOT NULL default '',
    `countryLONG` varchar(255) NOT NULL default '',
    `REGION` varchar(128) default NULL,
    `CITY` varchar(128) default NULL,
    `ISP_NAME` text,
    PRIMARY KEY (`ipFROM`,`ipTO`),
    KEY `ipFROM` (`ipFROM`),
    KEY `ipTO` (`ipTO`)
    ) TYPE=MyISAM;


    Number of records: 4,970,567

    Index is on ipFROM and ipTO fields.

    ipFROM and ipTO are integer numbers between 8 and 10 digits long.

    ----------------------------------------------------

    QUERY:

    SELECT countryLONG from ipcountry WHERE ipFROM <= 3521274926 AND ipTO >= 3521274926

    This query basically looks up a value which is located in a range between 2 values in one record.

    ----------------------------------------------------

    Any ideas how to speed it up?

    Thanks!

  • #2
    8 sec)

    I have tried this on several machines with different architectures and different configurations the result is the same .... please help me with this problem?

    /TedThe index is secondary in this case what is important is join order, which you can force with STRAIGHT_JOIN hint by the way.

    Note the order of tables becomes different and this is why.

    Scanning large table and doing single row lookups in tiny tables is faster than other way around. Quite expected. Thanks for your answer!

    With straight_join the question takes 7 s, but if you always use straigh

    Comment

    Working...
    X