MySQL Performance: MySQL => Query Speed Help

Query Speed Help [message #577]

Wed, 10 January 2007 16:44

Steele
Messages: 1
Registered: January 2007

Junior Member

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!

[Updated on: Thu, 11 January 2007 09:55]

Report message to a moderator