List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:November 19 2001 2:39am
Subject:Question about MySQL 4.0.0 change entry for "ORDER BY ... DESC can now use key"
View as plain text  

>>>>> "Jeremy" == Jeremy Zawodny <jzawodn@stripped> writes:

Jeremy> Internals folks,
Jeremy> I've noticed, in the change log for MySQL 4.0.0, the following
Jeremy> comment:

Jeremy>   ORDER BY ... DESC can now use key

Jeremy> Which I thought was going to address a common problem we have--an
Jeremy> excessive number of queries which "use filesort" because MySQL didn't
Jeremy> use indexes for descending sorts.  But it doesn't solve the problem
Jeremy> (for reasons that I'll go into in another message perhaps).

Jeremy> Then I came up with a case where I thought it'd make a big difference!
Jeremy> However, in playing with it I ran across a surprise--it still didn't
Jeremy> help.


Jeremy>   select * from BizHeadlines where Symbol = 'yhoo' order by Time desc

Jeremy> I get the filesort and it's not as fast.

Jeremy>         table: BizHeadlines
Jeremy>          type: ref
Jeremy> possible_keys: Symbol
Jeremy>           key: Symbol
Jeremy>       key_len: 75
Jeremy>           ref: const
Jeremy>          rows: 686
Jeremy>         Extra: where used; Using filesort

Jeremy> Grr. :-(

Jeremy> Does the new optimization in MySQL 4.0 only apply to indexes where
Jeremy> only the *first* key part is the one on which the order by is being
Jeremy> performed?  If so, is it possible to get my case optimized someday? If
Jeremy> not, this probably needs to be clearly documented somewhere.

The problem is that we are currently only supporting ORDER BY ... DESC
when the key is used in a range.  I will try to get time to fix your
case in MySQL 4.0.2

Until this, you can use the following query to go around this problem:

select * from BizHeadlines where Symbol between 'yhoo' and 'yhoo' order by Time desc


Jeremy> [1] the de-normalized table is there because MySQL has to filesort
Jeremy>     another common query, making it too slow for live use.  More on
Jeremy>     that if anyone wants to know.  It's a similar but different
Jeremy>     problem that I'd love to see addressed.  Then I wouldn't need this
Jeremy>     at all!

What is the other problem ?


For technical support contracts, goto
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Michael Widenius <monty@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
Question about MySQL 4.0.0 change entry for "ORDER BY ... DESC can now use key"Jeremy Zawodny16 Nov
  • Question about MySQL 4.0.0 change entry for "ORDER BY ... DESC can now use key"Michael Widenius19 Nov