I few random things come to mind... 1) Try the query with IGNORE INDEX calldate_idx ... I can't see how this could possibly be faster, but I always like to check anyway. In your case this should result in a full table scan, given the information you've given us.
2) If the performance problem comes from the date_format() function, there might not be much you can do about it, except to call it less often. Maybe you could add one or more WHERE clauses to restrict the range of rows you're looking at? (WHERE calldate > "2000-01-01 01:01:01" or something). 3) Obviously it'd probably be faster if you weren't using SQL_NO_CACHE... guessing you just did that to show us what it's like that way? 4) I'd check the values of sort_buffer_size and read_rnd_buffer_size. Perhaps you could benefit from raising them slightly. As for general performance tips, Here's a couple scripts I like to use that give some decent tuning advice: http://www.day32.com/MySQL/ http://wiki.mysqltuner.com/MySQLTuner Neither of these will be specific to this particular query of course. There is a query profiler tool in maatkit (http://www.maatkit.org/), however... might be worth a shot. Good luck, Jake On Sat, Dec 27, 2008 at 10:15 AM, 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? > > Chris > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=jakem...@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
