Hi!
>>>>> "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.
<cut>
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
<cut>
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 ?
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <monty@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
