MySQL Forums
Forum List  »  Performance

Please help :)
Posted by: Heather Walker
Date: April 18, 2005 04:18PM

I'm hoping someone can help me figure out what is wrong here. Here is the deal. I have a new IP-country database list that I put into a myisiam table. The problem is queries to locate a country using this table is taking MUCH longer than our current IP-country table, and I cannot figure out why. Using explain shows that MYSQL isn't using any keys when executing the query.

Here is the database structure:

CREATE TABLE `ipcountryNEW` (
`ipFROM` int(10) unsigned zerofill NOT NULL default '0000000000',
`ipTO` int(10) unsigned zerofill NOT NULL default '0000000000',
`countrySHORT` char(2) NOT NULL default '',
`countryLONG` varchar(255) NOT NULL default '',
`Region` varchar(128) NOT NULL default '',
`City` varchar(128) NOT NULL default '',
PRIMARY KEY (`ipFROM`,`ipTO`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
*************************************************************
Here is an example query:
mysql> select countryLONG from ipcountryNEW where 1142156934 between ipFROM AND ipTO;
+---------------+
| countryLONG |
+---------------+
| UNITED STATES |
+---------------+
1 row in set (4.04 sec)
*************************************************************
mysql> select count(*) from ipcountryNEW;
+----------+
| count(*) |
+----------+
| 1554025 |
+----------+
1 row in set (0.09 sec)
*************************************************************
--Even if I try a little different query, I still don't get the engine to use the key...

mysql> EXPLAIN select countryLONG from ipcountryNEW where ipFROM>=1142156934 AND ipTO<=1142156934;
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | ipcountryNEW | ALL | PRIMARY | NULL | NULL | NULL | 1554025 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.09 sec)
*************************************************************
mysql> explain select countryLONG from ipcountryNEW where 1142156934 between ipFROM AND ipTO;
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | ipcountryNEW | ALL | NULL | NULL | NULL | NULL | 1554025 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.24 sec)
*************************************************************

Options: ReplyQuote


Subject
Views
Written By
Posted
Please help :)
2272
April 18, 2005 04:18PM
1579
April 21, 2005 03:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.