At 11:36 AM 5/23/2003, Egor Egorov wrote:
>On Friday 23 May 2003 18:11, Jason Hamilton wrote:
> > At 11:01 AM 5/23/2003, Egor Egorov wrote:
> > > > I'm having a problem with MySQL and ORDER BY.
> > > >
> > > > With this statement, extra = "Using where"
> > > >
> > > > EXPLAIN SELECT * FROM c1 LEFT JOIN c2 ON c1.id = c2.id WHERE c1.id =
> > > > '21'
> > > >
> > > > Simply adding "ORDER BY c1.size DESC" changes the extra
> > > > to "Using where; Using filesort".
> > > >
> > > > Naturally I have an index on c1.id (c2.id also has an index), and
> > > > c1.size.
> > > >
> > > > I'm at a loss as to why it's going to filesort. The query speed
> > > > is quick on most results, except for when there is > ~10,000
> matches,
> > > > then it can take several seconds to process. Most matches are
> > > > under 1,000 results, though some can be as high as 150,000.
> > > >
> > > > Is there anything I can do to remove use of filesort, or increase
> > > > the speed of the query on large matches?
> > >
> > >What version do you use? 3.23.xx ? If so, you can upgrade to 4.0.x. In
> > > 4.0.x MySQL can use index in ORDER BY .. DESC queries.
> >
> > Egor,
> >
> > I'm running mysql-standard-4.0.12. The machine is a 2.4ghz server w/
> > 1 gig of ram. I have been screwing around with the conf settings, heres
> > some of the settings:
>
>Do you have separate indexes on c1.id and c1.size or multi-column index on
>(c1.id, c1.size) ?
I have a index on c1.id, AND c1.size. I also have a multi col index on
c1.id,c1.size.
I also tried the query without the LEFT JOIN, with the same result - using
filesort.
