MySQL Forums
Forum List  »  MyISAM

How do I get mysql to use the keys?
Posted by: Heather Walker
Date: April 18, 2005 06:46PM

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 it doesn't seem to be using the key I created. 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
How do I get mysql to use the keys?
3096
April 18, 2005 06:46PM


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.