Jump to content


Photo

MySQL ORDER BY optimization?


  • Please log in to reply
1 reply to this topic

#1 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 April 2006 - 05:38 PM

[!--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?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 May 2006 - 06:53 AM

*BUMP*

Someone must know something about this.....
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users