Jump to content


This topic is now archived and is closed to further replies.


MySQL ORDER BY optimization?

Recommended Posts

[!--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?

Share this post

Link to post
Share on other sites

Someone must know something about this.....

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.