Hi, On Sat, Dec 27, 2008 at 6:15 PM, Chris Picton <ch...@ecntelecoms.com> 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=ewen.fort...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
