表结构
===============================
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请问应该如何建索引?????????????
===============================
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请问应该如何建索引?????????????
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ip system PRIMARY,Index_2 (NULL) (NULL) (NULL) 1 我这边有用啊 。
EXPLAIN的输出显示了当MySQL使用表扫描来解决查询时使用的所有类型列。这通常在如下条件下发生:· 表很小,扫描表比查找关键字速度快。这对于少于10行并且行较短的表比较普遍。· 在ON或WHERE子句中没有适用的索引列的约束。· 正用常量值比较索引列,并且MySQL已经计算到(基于索引树)常数覆盖了表的很大部分并且表扫描将会比较快。参见7.2.4节,“MySQL怎样优化WHERE子句”。· 你正通过另一个列使用一个低的集的势的关键字(许多行匹配关键字)。在这种情况下,MySQL假设通过使用关键字它可能会进行许多关键字查找,表扫描将会更快。对于小表,表扫描通常合适。对于大表,尝试下面的技巧以避免优化器错选了表扫描:· 使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。参见13.5.2.1节,“ANALYZE TABLE语法”。· 对扫描的表使用FORCE INDEX告知MySQL,相对于使用给定的索引表扫描将非常耗时。参见13.2.7节,“SELECT语法”。· SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
· WHERE t1.col_name=t2.col_name;
· 用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。参见5.3.3节,“服务器系统变量”。
分别用了 PRIMARY 和 index 2 ,
key : NULL
rows : 290179
-------------
总行数就是290179,说明还是表扫描