How do I get mysql to use the keys?
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)
*************************************************************
Subject
Views
Written By
Posted
How do I get mysql to use the keys?
3272
April 18, 2005 06:46PM
2373
April 25, 2005 02:35AM
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.