Re: Performance tuning - comparing two computers
Posted by:
Rick James
Date: January 24, 2013 10:38PM
> SHOW STATUS LIKE 'HANDLER%' is the same on both PC's, all variable values are zero.
Do it before _and_ after running the SELECT.
> Both EXPLAIN results are the same, except the the new PC "rows" is 140,000 less (PC1: 1,805,004/ PC2: 1,665,117), which I can't "explain", because the select request on both computers read 1,256,327 rows.
EXPLAIN is approximate. Those numbers (and the fact that they are different) are well within normal variations.
Please provide
* entire output from the EXPLAIN.
* SHOW CREATE TABLE -- need to see indexes, etc
* SHOW TABLE STATUS -- need to see the table size
Is there an index starting with CYCLE_DATE_I, and does EXPLAIN claim to use it?
It sounds like you are into Data Warehousing; you should learn about Summary Tables. The can easily speed up queries like that by 10-fold.
> only two diffrences - innodb_file_per_table and innodb_log_file_size
Not likely to matter (in this case).
I see that the OS is 64-bit, but is the mysql binary also 64-bit?