René Fournier <[EMAIL PROTECTED]> wrote on 11/17/2005 04:19:25 PM: > OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- > optimization.html I have learned improved the speed somewhat by > creating a multi-column index on account_id and time_sec, such that: > > ------------------------------------------------------------------------
> ------ > SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month > FROM history > WHERE history.account_id = 216 > GROUP BY month > ORDER BY history.time_sec DESC > > Showing rows 0 - 5 (6 total, Query took 0.0267 sec) > ------------------------------------------------------------------------ > ------ > > So, about 15x faster. But if I drop "GROUP BY month", it goes really > fast: > > ------------------------------------------------------------------------ > ------ > SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month > FROM history > WHERE history.account_id = 216 > ORDER BY history.time_sec DESC > > Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) > ------------------------------------------------------------------------ > ------ > > Is it possible to have the "GROUP BY month" as part of a multi-column > index? Or do something to get the speed closer to 0.0009 sec? > > ...Rene > You are already pushing the limits of total response time: submit time + parse/validate time + optimization + execution (including: date conversion, grouping, and re-sorting) + formating and transmitting output ===================================== = .0267 sec If a network or disk access is involved in any part of that chain, I think you are optimal (it's hard to get some pings to return in sub .03 seconds). Have you thought about storing your data into a HEAP or MEMORY table? That might get more speed out of it. As would using a prepared statement (reducing the parse/validate portion of the equation). Why is this query so time-critical, if I may ask? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
