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=arch...@jab.org
