List:General Discussion« Previous MessageNext Message »
From:Mark Leith Date:December 17 2006 12:06pm
Subject:Re: Sort Select by List
View as plain text  
Dan Nelson wrote:
> --snip--
>> More efficient would be to use the FIELD function:
>>
>> SELECT * FROM team ORDER BY FIELD(office,"Exec","VP","Dir");
>>     
>
> Oops.  I only read the replies and not the original post.  Assuming
> there are many other values for the "office" field, you might want
>
> SELECT * FROM team WHERE office = 'Exec' UNION
> SELECT * FROM team WHERE office = 'VP'  UNION
> SELECT * FROM team WHERE office = 'Dir';
>   

Indeed! :) Especially if the table is of any decent size (I assumed that 
it was not *huge*). The UNION will give index accesses, the "tricks" on 
the ORDER BY will cause filesorts (although, they will still likely be 
in memory unless you the table is large, again).

You should of course EXPLAIN and tune accordingly (with response times 
as well).

Cheers,

Mark

-- 
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified?  www.mysql.com/certification

Thread
Sort Select by ListKeith Spiller16 Dec
  • Re: Sort Select by ListKeith Spiller16 Dec
  • Re: Sort Select by ListPintér Tibor16 Dec
    • Re: Sort Select by ListMark Leith16 Dec
      • Re: Sort Select by ListDan Nelson17 Dec
        • Re: Sort Select by ListDan Nelson17 Dec
          • Re: Sort Select by ListMark Leith17 Dec
  • Re: Sort Select by ListKeith Spiller17 Dec