List:General Discussion« Previous MessageNext Message »
From:ewen fortune Date:December 27 2008 7:22pm
Subject:Re: Performance tips
View as plain text  
Mmm I just tested this and it does indeed work (although i tested with
slightly less rows :o) )


explain select count(*) , date_format(calldate, '%y-%m-%d') as m from
cdr_warehouse group by m \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cdr_warehouse
         type: index
possible_keys: NULL
          key: calldate_idx
      key_len: 4
          ref: NULL
         rows: 26
        Extra: Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

explain select count(*) , date_format(calldate, '%y-%m-%d') as m from
cdr_warehouse group by calldate \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cdr_warehouse
         type: index
possible_keys: NULL
          key: calldate_idx
      key_len: 4
          ref: NULL
         rows: 26
        Extra: Using index

Cheers,

Ewen

On Sat, Dec 27, 2008 at 8:04 PM, ewen fortune <ewen.fortune@stripped> wrote:
> Hi,
>
> On Sat, Dec 27, 2008 at 6:15 PM, Chris Picton <chris@stripped> wrote:
>> Hi
>>
>> I am trying to get to grips with understanding mysql performance.
>>
>> I have the following query:
>>
>> select sql_no_cache count(*), date_format(calldate, '%y-%m-%d') as m from
>> cdr_warehouse group by m;
>>
>> This gives me:
>> 115 rows in set (59.52 sec)
>>
>>
>> mysql> explain select count(*), date_format(calldate, '%y-%m-%d') as m from
>> cdr_warehouse group by m\G
>> *************************** 1. row ***************************
>>           id: 1
>>  select_type: SIMPLE
>>        table: cdr_warehouse
>>         type: index
>> possible_keys: NULL
>>          key: calldate_idx
>>      key_len: 8
>>          ref: NULL
>>         rows: 43708571
>>        Extra: Using index; Using temporary; Using filesort
>> 1 row in set (0.00 sec)
>>
>>
>> mysql> show keys from cdr_warehouse \G;
>> *************************** 1. row ***************************
>>       Table: cdr_warehouse
>>  Non_unique: 1
>>    Key_name: uploaded_idx
>> Seq_in_index: 1
>>  Column_name: uploaded
>>   Collation: A
>>  Cardinality: 66
>>    Sub_part: NULL
>>      Packed: NULL
>>        Null:
>>  Index_type: BTREE
>>     Comment:
>> *************************** 2. row ***************************
>>       Table: cdr_warehouse
>>  Non_unique: 1
>>    Key_name: calldate_idx
>> Seq_in_index: 1
>>  Column_name: calldate
>>   Collation: A
>>  Cardinality: 5526774
>>    Sub_part: NULL
>>      Packed: NULL
>>        Null:
>>  Index_type: BTREE
>>     Comment:
>>
>>
>> How can I improve the running speed of this query?  I am running 5.1.30, but
>> don't (yet) want to partition the table (horizontally or vertically).
>>  Nothing else on the server is touching this table at the moment.  The exact
>> date_format is not important, as I may want to group by 5 second intervals,
>> or full months.
>>
>> Any tips/ideas for me?
>
> Have you tried doing GROUP BY calldate ?
> select sql_no_cache count(*), date_format(calldate, '%y-%m-%d') as m from
> cdr_warehouse group by calldate;
>
> This may allow you to avoid the filesort by using the index directly
> for ordering.
>
> Also I presume this is MyISAM to avoid the InnoDB without WHERE COUNT(*) issue.
>
>
> Ewen
>
>>
>> Chris
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>
Thread
Performance tipsChris Picton27 Dec
  • Re: Performance tipsJake Maul27 Dec
    • Re: Performance tipsJim Lyons30 Dec
      • Re: Performance tipsBaron Schwartz31 Dec
  • Re: Performance tipsDan Nelson27 Dec
  • Re: Performance tipsewen fortune27 Dec
    • Re: Performance tipsewen fortune27 Dec