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 <[email protected]> wrote:
> Hi,
>
> On Sat, Dec 27, 2008 at 6:15 PM, Chris Picton <[email protected]> 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/[email protected]
>>
>>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
