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/mysql?unsub=1
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
