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
>
>
