MySQL ORDER BY optimization?

[fenway]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: ... You are joining many tables, and the columns in the ORDER BY are not all from the first non-constant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.) [/quote]
So I know all about adding indexes for use with ORDER BY, multi-column keys, preventing table scans, preventing table reads at all, etc.

But I haven't been able to work around this particular issue -- in fact, I didn't even know it wasn't possible until I read the refman. This is, of course, independent of version.

Basically, if you use JOINs of any type -- like a 4-table LEFT JOIN -- you will be unable to sort on any keys that do not appear in the "first" non-joined table. I understand in principle why this might be difficult, because you can't request an order for the rows to be joined in, but still, it's troublesome.

I was thinking that perhaps I could use subqueries to work around this -- perhaps if there was just a single ORDER BY column, it could rewrite this, but it gets cumbersome with 3 such columns. And besides, I prefer JOINs anyway, if possible.

Any ideas? Anyone else run into this problem?

[fenway]

*BUMP*

Someone must know something about this.....