Buding,
Monday, March 04, 2002, 3:37:08 PM, you wrote:
BC> Hi, all:
BC> I create a table as following:
BC> CREATE TABLE appsvr_trarte (
BC> rte_id bigint(38) NOT NULL auto_increment,
BC> strConfName varchar(70) NOT NULL default '',
BC> lFromTime int(16) unsigned NOT NULL default '0',
BC> lToTime int(16) unsigned NOT NULL default '0',
BC> iSvrType tinyint(2) unsigned NOT NULL default '0',
BC> sSrvIP int(10) unsigned NOT NULL default '0',
BC> sClitIP int(10) unsigned NOT NULL default '0',
BC> iSpeed int(10) unsigned NOT NULL default '0',
BC> PRIMARY KEY (rte_id),
BC> KEY idx_trarte (lFromTime,sSrvIP,sClitIP)
BC> ) TYPE=MyISAM;
BC> However, after INSERT into some data( lFromTime,sSrvIP,sClitIP are not
BC> unique ), I am astonished by the following:
BC> mysql> explain SELECT lFromTime ,iSvrType,iSpeed FROM appsvr_trarte where
BC> lFromTime >= 1009818000 and lFromTime < 1017594000;
BC> +----------------+------+---------------+------+---------+------+--------+--
BC> ----------+
BC> | table | type | possible_keys | key | key_len | ref | rows |
BC> Extra |
BC> +----------------+------+---------------+------+---------+------+--------+--
BC> ----------+
BC> | appsvr_trarte | ALL | idx_trarte | NULL | NULL | NULL | 168359 |
BC> where used |
BC> +----------------+------+---------------+------+---------+------+--------+--
BC> ----------+
BC> 1 row in set (0.00 sec)
BC> I have used /usr/local/mysql/bin/myisamchk -a appsvr_trarte , but no
BC> effect.
BC> I wonder why MySql didn't use index while querying? And how to resolve
BC> it?
MySQL doesn't use indexes if result of query is more than 30% rows in
the table. It works much faster without using indexes.
You can read about how MySQL uses indexes at:
http://www.mysql.com/doc/M/y/MySQL_indexes.html
BC> B.R.
BC> Buding
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
