List:General Discussion« Previous MessageNext Message »
From:René Fournier Date:November 17 2005 9:52pm
Subject:Re: SELECT with WHERE and GROUP BY -- can it go any faster?
View as plain text  
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

Thread
SELECT with WHERE and GROUP BY -- can it go any faster?René Fournier17 Nov
  • Re: SELECT with WHERE and GROUP BY -- can it go any faster?René Fournier17 Nov
    • Re: SELECT with WHERE and GROUP BY -- can it go any faster?SGreen17 Nov
      • Re: SELECT with WHERE and GROUP BY -- can it go any faster?René Fournier17 Nov