I have a query that works fine until my date range goes one day over some kind of data size threshold & then it mysteriously goes from 0.34 seconds to 30 seconds. My DBA pointed out that my SQL syntax was loose & that Oracle would've flagged it as an error. I did a GROUP BY based on an numeric ID field & then afterwards did an ORDER BY on a string name field. When I adjusted the query to GROUP BY name and numeric ID then ORDER BY name it's much faster even past the threshold date. I thought that doing the GROUP BY only on a numeric ID would be faster than doing it by string and then ID. Should MySQL have flagged this as an error, should it've optimized this by itself, or is my SQL too loose ? - Sam. Slow : SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 1029 AND a.rpt_dt >= '2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY a.metro_id ORDER BY m.metro_name; Faster : explain SELECT m.metro_name, sum(a.td_num), a.metro_id FROM album_by_metro a, usa_metro_ref m WHERE a.metro_id= m.metro_id AND a.same_album_id = 1029 AND a.rpt_dt >= '2001-01-01' AND a.rpt_dt <= '2001-05-14' GROUP BY m.metro_name,a.metro_id ORDER BY m.metro_name; --------------------------------- Do You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell?
