List:General Discussion« Previous MessageNext Message »
From:Chris Picton Date:December 27 2008 5:15pm
Subject: Performance tips
View as plain text  
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?

Chris

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