Hi,all: Thanks to Egor Egorov, you are right. Also thanks to Rob Emerick. Unfortunately, I encountered another problem as following:
mysql> explain SELECT max(iSpeed),avg(iSpeed) FROM appsvr_trarte where lFromTime >= 1009818000 and lFromTime < 1012496400 and iSvrType = 33; +---------------+-------+---------------+------------+---------+------+----- --+------------+ | table | type | possible_keys | key | key_len | ref | rows |Extra | +---------------+-------+---------------+------------+---------+------+----- --+------------+ | appsvr_trarte | range | idx_trarte | idx_trarte | 5 | NULL | 13106 |where used | +---------------+-------+---------------+------------+---------+------+----- --+------------+ 1 row in set (0.00 sec) mysql> explain SELECT max(iSpeed),avg(iSpeed) FROM appsvr_trarte where lFromTime >= 1009818000 and lFromTime < 1017594000 and iSvrType = 33 ; +---------------+------+---------------+------+---------+------+--------+--- ---------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------------+------+---------------+------+---------+------+--------+--- ---------+ | appsvr_trarte | ALL | idx_trarte | NULL | NULL | NULL | 168359 | whereused | +---------------+------+---------------+------+---------+------+--------+--- ---------+ 1 row in set (0.00 sec) However, the former query(SELECT max(iSpeed),avg(iSpeed) FROM appsvr_trarte where lFromTime >= 1009818000 and lFromTime < 1012496400 and iSvrType = 33) costs 768ms while the latter query(SELECT max(iSpeed),avg(iSpeed) FROM appsvr_trarte where lFromTime >= 1009818000 and lFromTime < 1017594000 and iSvrType = 33) costs 719ms. I use C API and gettimeofday() to test it. It seems that using index doesn't improve the query speed, and the latter affect 168359 rows while the former query affect only 13106 rows ! BTW, could you tell me how to display the interval time spent in querying directly without other result in mysql>. Thanks to any help. B.R. Buding ----- Original Message ----- From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 04, 2002 11:56 PM Subject: Why can't use INDEX while querying? > 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 > --------------------------------------------------------------------- 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