Am 29.06.2006 um 17:17 schrieb Dennis Cote:
Jens Miltner wrote:
Is there any way to improve the ORDER BY performance for joined
queries? From your answer that the intermediate results are
sorted, I take it no index won't ever be used when using ORDER BY
with a join query?
Is there a way to rewrite the queries so we don't take the penalty
of sorting without an index?
In our case, the 'bar' table may have several 100,000 entries and
the 'foo' table probably has much less (say order of thousand).
A minimal query (i.e. one where we don't return a minimal amount
of data, so the pure data shuffling doesn't get in the way) with a
'bar' table with 250,000 entries and a 'foo' table with around 10
entries (so the 250,000 entries in the 'bar' table relate to only
a few records in the 'foo' table), a query like the above takes
10-20 minutes to run (depending on the ORDER BY clauses used)!
(Side-note: The table does have quite a few columns and
apparently, the amount of data per row also massively affects the
performance... when I tried with the same table with not all
columns filled in - i.e. less data - performance was much better.
I also tried increasing the page size, hoping that less paging
would have happen, but this didn't really make a noticeable
difference)
I would really appreciate any hints on how to improve performance
with this kind of setup...
Jens,
If you create an index on your bar.something column, it will be
used to do the ordering. The log below shows how this query will be
executed before and after creating this index.
Doh! You're right... I could have sworn when I tried, it didn't show
an index on bar(something) to be used...
I probably confuse this with our real schema & query, which is
slightly more complex and which I couldn't get to use an index on the
ORDER BY column(s)...
Maybe it's because there sometimes are more than one sort column,
even from distinct tables. I guess in that case, I'm probably stuck
without an index?
Thanks for your help so far,
</jum>
