| 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]
|
|
|
| Re: Query Speed Help [message #578 is a reply to message #577 ] |
Wed, 10 January 2007 16:54  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
