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
