You are correct. On Wed, 24 Mar 2004, Henrik Schröder wrote: > 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]
