----- Original Message ----- From: "Joseph Bueno" <[EMAIL PROTECTED]> To: "David BORDAS" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, April 19, 2002 10:34 AM Subject: Re: Select with Order By that don't use my INDEX :(
> David BORDAS a écrit : > > > > I'm using mysql 3.23.49 on Linux redhat dedicated server. > > > > Here is my query : > > > > mysql> show index from MyTable; > > ############################ > > Table : MyTable > > Non_unique : 0 > > Key_name : PRIMARY > > Seq_in_index : 1 > > Column_name : Field0 > > Collation : A > > Cardinality : NULL > > Sub_part : NULL > > Packed : NULL > > Comment : NULL > > > > ############################# > > Table : MyTable > > Non_unique : 0 > > Key_name : Index1 > > Seq_in_index : 1 > > Column_name : Field3 > > Collation : A > > Cardinality : NULL > > Sub_part : NULL > > Packed : NULL > > Comment : NULL > > > > Table : MyTable > > Non_unique : 0 > > Key_name : Index1 > > Seq_in_index : 2 > > Column_name : Field0 > > Collation : A > > Cardinality : NULL > > Sub_part : NULL > > Packed : NULL > > Comment : NULL > > > > ############################# > > Table : MyTable > > Non_unique : 0 > > Key_name : questions > > Seq_in_index : 1 > > Column_name : Field2 > > Collation : A > > Cardinality : NULL > > Sub_part : NULL > > Packed : NULL > > Comment : NULL > > > > Table : MyTable > > Non_unique : 0 > > Key_name : questions > > Seq_in_index : 2 > > Column_name : Field3 > > Collation : A > > Cardinality : NULL > > Sub_part : NULL > > Packed : NULL > > Comment : NULL > > > > Table : MyTable > > Non_unique : 0 > > Key_name : questions > > Seq_in_index : 3 > > Column_name : Field4 > > Collation : A > > Cardinality : NULL > > Sub_part : NULL > > Packed : NULL > > Comment : NULL > > > > MyTable info : > > Field0 : int(10) unsigned primary key > > Field1 : int(10) unsigned > > Field2 : int(10) unsigned > > Field3 : int(10) unsigned > > Field4 : datetime Null: Yes default 0000-00-00 00:00:00 ( but this field > > don't have null values ) > > Field5 : varchar(50); > > > > mysql> explain SELECT * FROM Forums WHERE Field2=5020 AND Field3=0 ORDER BY > > Field4 DESC LIMIT 0,20; > > ************** > > table : MyTable > > type : ref > > possible_keys : Index1,questions > > key : questions > > key_len : 1 > > ref : const,const > > rows : 390 > > Extra : where used; Using filesort > > 1 row in set (0.00 sec) > > > > We can see that index questions which have Fields 2, 3 and 4 isn't use for > > the order by. > > Why ? > > > > Thanks in advance > > David > > > > --------------------------------------------------------------------- > > 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > Hi, > > MySQL 3.23.x doesn't use the index with DESC option of order by. > It seems that it is fixed in 4.x but I haven't tested it yet. > > Hope this helps > -- > Joseph Bueno > NetClub/Trader.com > Arf, i've tried with an ASC query and index is used :( now i must wait a new mysql release :( Thanks --------------------------------------------------------------------- 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
