Query Speed Help

MySQL & MariaDB Other MySQL® Questions
1

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