On 17-Nov-05, at 2:41 PM, SGreen@stripped wrote:
> René Fournier <m5@stripped> 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?
Well, I have a number of queries that are executed on every page, and
I'm just trying to optimize them. I don't presume to be a DBA, but I
would like to learn how to tune these queries as much as possible...
Thanks for your response. Maybe 0.0267 seconds is as good as it gets.
...Rene
