List:General Discussion« Previous MessageNext Message »
From:ewen fortune Date:December 27 2008 7:04pm
Subject:Re: Performance tips
View as plain text  
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