Re: Performance tuning - comparing two computers
The SQL statements:
RESET QUERY CACHE;
FLUSH QUERY CACHE;
FLUSH STATUS;
SHOW STATUS LIKE 'Handler%';
SELECT FUND, SUM(PURCHASES) FROM f_transaction_level1
WHERE CYCLE_DATE_I >= 20100101 AND CYCLE_DATE_I < 20120101
GROUP BY FUND
ORDER BY FUND ;
SHOW STATUS LIKE 'Handler%';
The STATUS (same for both machines):
Handler_commit,1
Handler_delete,0
Handler_discover,0
Handler_prepare,0
Handler_read_first,0
Handler_read_key,4142976
Handler_read_last,0
Handler_read_next,4142975
Handler_read_prev,0
Handler_read_rnd,265
Handler_read_rnd_next,266
Handler_rollback,0
Handler_savepoint,0
Handler_savepoint_rollback,0
Handler_update,4142710
Handler_write,265
The table status:
Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Collation,Checksum,Create_options,Comment
f_transaction_level1,InnoDB,10,Compact,6506209,164,1070596096,0,1611661312,481296384,NULL,"2012-12-28 14:11:14",NULL,NULL,latin1_swedish_ci,NULL,,
The explain result (same for both):
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,f_transaction_level1,range,"PRIMARY,TxIdx5",PRIMARY,4,NULL,3253104,"Using where; Using temporary; Using filesort"
The CREATE table statement:
CREATE TABLE `f_transaction_level1` (
`CYCLE_DATE_I` int(11) NOT NULL,
`FUND` varchar(4) NOT NULL,
`CLASS` varchar(2) NOT NULL,
`AGENCYCODE` varchar(6) NOT NULL,
`BRANCH` varchar(6) NOT NULL,
`AGENTCODE` varchar(6) NOT NULL,
`PURCHASES` decimal(21,2) NOT NULL,
`REDEMPTIONS` decimal(21,2) NOT NULL,
`TRANSFERS` decimal(21,2) NOT NULL,
`TRANSFERSIN` decimal(21,2) NOT NULL,
`TRANSFERSOUT` decimal(21,2) NOT NULL,
`NETSALES` decimal(21,2) NOT NULL,
`SWITCHES` decimal(21,2) NOT NULL,
`SWITCHESIN` decimal(21,2) NOT NULL,
`SWITCHESOUT` decimal(21,2) NOT NULL,
`TOTALTRANS` decimal(21,2) NOT NULL,
PRIMARY KEY (`CYCLE_DATE_I`,`FUND`,`CLASS`,`AGENCYCODE`,`BRANCH`,`AGENTCODE`),
KEY `TxIdx2` (`FUND`,`CLASS`),
KEY `TxIdx1` (`AGENCYCODE`,`BRANCH`,`AGENTCODE`),
KEY `TxIdx3` (`AGENCYCODE`),
KEY `TxIdx4` (`AGENCYCODE`,`BRANCH`),
KEY `TxIdx5` (`CYCLE_DATE_I`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CYCLE_DATE_I is a primary key, but \i'm sorry to say I don't know how to read the EXPLAIN result.
Version info - so, NOT 64-bit! I should look into that! I'm sure I installed my instance of MySQL from mysql-5.5.22-winx64.msi.
version,5.5.22-log
version_comment,"MySQL Community Server (GPL)"
version_compile_machine,x86
version_compile_os,Win64
Yes, Rick, I really need to learn about "Summary Tables", specialy since we're calling tables like this one "summary tables". I'll read up about this.
Thanks for the suggestions.
Francis
Edited 1 time(s). Last edit at 01/25/2013 08:05PM by Francis Mariani.