I don't think it does right now, but that can always be taken care of later.
Assuming that the query uses one index that contains all the where-columns and the orderby-columns, having an ORDER BY that sorts on expressions is still the biggest problem, right? /Henrik -----Original Message----- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: den 24 mars 2004 16:22 To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]' Subject: Re: How to optimize ugly order by? Let me rephrase my statement. Does the index being used include the order by columns? If not, the optimizer will not be able to perform both row selection and row ordering in the same pass. On Wed, 24 Mar 2004, Victor Pendleton wrote: > Does the where clause contain the order by columns? If not, then you > will have a filesort no matter what. > > On Wed, 24 Mar 2004, Henrik Schröder wrote: > > Hi all, > > > > I have a table of users which contains a lot of information, and > > also a column called membershiptype which can either be 0, 1, or 2. > > This table is used a lot in various searches, and all results should > > be ordered by membership type, but the order should not be 0-1-2, > > but instead 1-2-0. Currently, this is achieved like this: > > > > SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) > > ASC, login ASC > > > > ...which is rather ugly, and forces MySQL to create a temp table > > with tthe calculated expressions and then re-sort the result using > > these. Since this query is used a lot, it would be nice if I could > > get rid of this. I'm completely stumped. Any ideas? > > > > No, I did not choose the sort order. No, I can not change the values > > used. Yes, it has to be this sort order. :-) > > > > > > /Henrik Schröder > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
