Re: Performance of ORDER BY

Glenn,

On 12/5/06 9:12 AM, "Glenn Sullivan" <glenn(dot)sullivan(at)varianinc(dot)com> wrote:

> I am wanting some ideas about improving the performance of ORDER BY in
> our use. I have a DB on the order of 500,000 rows and 50 columns.
> The results are always sorted with ORDER BY. Sometimes, the users end up
> with a search that matches most of the rows. In that case, I have a
> LIMIT 5000 to keep the returned results under control. However, the
> sorting seems to take 10-60 sec. If I do the same search without the
> ORDER BY, it takes about a second.
>
> I am currently on version 8.0.1 on Windows XP using a Dell Optiplex 280
> with 1Gb of ram. I have set sort_mem=100000 set.
>
> Any ideas?

Upgrade to 8.1 or 8.2, there were very large performance improvements to the
sort code made for 8.1+. Also, when you've upgraded, you can experiment
with increasing work_mem to get better performance. At some value of
work_mem (probably > 32MB) you will reach a plateau of performance, probably
4-5 times faster than what you see with 8.0.

- Luke