日期:2014-05-16  浏览次数:20810 次

mysql 索引问题, 建上索引,怎么不管用呀??
表结构
===============================
  CREATE   TABLE   `ip`   (
    `onip`   int(10)   unsigned   NOT   NULL,
    `offip`   int(10)   unsigned   NOT   NULL,
    `district`   varchar(45)   NOT   NULL,
    `address`   varchar(200)   default   NULL,
    PRIMARY   KEY     (`onip`),
    UNIQUE   KEY   `Index_2`   (`offip`)
)   ENGINE=MyISAM   DEFAULT   CHARSET=utf8   ;


似乎没有使用索引
===============================
mysql>   EXPLAIN   SELECT   district,address   FROM   ip   WHERE   onip <2130706433;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|   id   |   select_type   |   table   |   type   |   possible_keys   |   key     |   key_len   |   ref     |   rows       |   Extra               |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|     1   |   SIMPLE             |   ip         |   ALL     |   PRIMARY               |   NULL   |   NULL         |   NULL   |   290179   |   Using   where   |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1   row   in   set   (0.00   sec)


mysql>   EXPLAIN   SELECT   district,address   FROM   ip   WHERE   offip> 2130706433;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|   id   |   select_type   |   table   |   type   |   possible_keys   |   key     |   key_len   |   ref     |   rows       |   Extra               |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|     1   |   SIMPLE             |   ip         |   ALL     |   Index_2               |   NULL   |   NULL         |   NULL   |   290179   |   Using   where   |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1   row   in   set   (0.00   sec)


mysql>   select   count(*)   from   ip;
+----------+
|   count(*)   |
+----------+
|       290179   |
+----------+
1   row   in   set   (0.00   sec)

难的索引建错了??      

我的查询语句是:
SELECT   district,address   FROM   ip   WHERE   onip <=2130706433   AND   offip> =2130706433

请问应该如何建索引?????????????


------解决方案--------------------
query result(1 records)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ip system PRIMARY,Index_2 (NULL) (NULL) (NULL) 1