Re: When are Temporary Files Required For: Order By

>I have the following select: > select col_char_11, col_date, col_decimal_11_0, col_char_3,>col_serial > from table > where col_char_11 = "01234567891" > and col_date between "01/01/1999" and "31/01/1999" > and col_decimal_11_0 > 0 > order by col1, col2, col3. > >I have 2 indexes on the table: a composite on col_char_11, col_date, >col_decimal_11_0 and > a unique index on col_serial > >When I look at the sqexplain.out file, I get the output you would expect for >the above query i.e.. >Index being used. Estimated cost = 1. Estimated rows returned = 1 etc. >This is all well and good. > >When I change the order by to order by col_serial >I get the same output but this time it says >Temporary Files Required For: Order By > >My question is >Why are Temporary Files required when I order by a serial column and not >when I order by >the columns in the composite index ? Probably because the where clause in the first one contains the same columns as the order by. What could be horrendous is that the second one would sort the entire table before applying the where clause, possibly leading to a much slower query. HTH. -- "I'll Be Back" Obnoxio ************************************************ Sane? Hell, if I was sane, why would I be here? Get Your Private, Free Email at http://www.hotmail.com