MySQL Forums
Forum List  »  Performance

Re: Performance tuning - comparing two computers
Posted by: Francis Mariani
Date: January 25, 2013 07:59PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Performance tuning - comparing two computers
1069
January 25, 2013 07:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.