List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:July 14 2005 6:34pm
Subject:Re: Null & alphabetic order
View as plain text  
Mark Leith wrote:

>>From: Michael Stassen [mailto:mstassen@stripped] 
<snip>
>>Something like
>>
>>   ORDER BY IF(col IS NULL, 1, 0), col
>>
>>Michael
> 
> Or simply:
> 
> ORDER BY col IS NULL, col
> 
> Which will probably be *slightly* faster..

Good point.

Михаил Монашёв wrote:
 > Hello
 >
 > MS> Something like
 >
 > MS>    ORDER BY IF(col IS NULL, 1, 0), col
 >
 > it's very slowly. Maybe better create 'col', and make index after?
 >
 > Sincerely,
 > Михаил Монашёв,

I don't think an index will help here, as the first part of the ORDER BY, "col 
IS NULL", causes a filesort.  If there is an index on col, and if the query is 
one that would otherwise use it, this might be faster

   (SELECT col FROM mytable WHERE col IS NOT NULL ORDER BY col)
  UNION ALL
   (SELECT col FROM mytable WHERE col IS NULL);

but it's a little silly.  The real query no doubt selects other columns, and 
has WHERE conditions on other columns, so mysql will probably not use an index 
on col to order the results anyway.  Perhaps a multi-column covering index 
would help, depending on the specifics of the real query.

Michael
Thread
Null & alphabetic orderScott Hamm13 Jul
  • Re: Null & alphabetic orderMichael Stassen13 Jul
    • RE: Null & alphabetic orderMark Leith13 Jul
      • Re: Null & alphabetic orderMichael Stassen14 Jul