On Friday 23 May 2003 08:33, Jason Hamilton 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.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ Egor.Egorov@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
