Subject: Re: Select with Order By that don't use my INDEX :( From: Vic Cekvenich <[EMAIL PROTECTED]> === Better yet, remove the order by. Make it sort by the index, ie, create an index that matches your select. Vic
Dr. Frank Ullrich wrote: > David, > > it can't because this field is not the first one in your concat index! > > Regards, > Frank. > > David BORDAS wrote: > >>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 >> > --------------------------------------------------------------------- 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
