Re: Performance tuning - comparing two computers
Posted by:
Rick James
Date: January 26, 2013 12:11AM
These numbers continually increase:
SHOW STATUS LIKE 'Handler%';
so you need to run it both before and after the SELECT, then see how much the values increased. Meanwhile, assuming they started with zero, I see that it scanned through 4 million rows. Could there be that many rows in 2010 and 2011?
The EXPLAIN picked the PRIMARY KEY and estimated 3253104 rows. (That is close enough to agree with the 4M.)
GROUP BY FUND caused the filesort.
If mysql is 32-bit, then this won't work, but I don't know what will happen:
innodb_buffer_pool_size,4294967296
innodb_flush_log_at_trx_commit,1 -- 1 is more secure, but slower than 2 -- you have a difference here.
innodb_log_file_size,1073741824 is quite different. It _might_ matter for a 4-million row query.
innodb_file_per_table,ON is better. (But won't explain the speed diff.)
Look for more differences and study what they mean.
A "summary table" (for this case) would have daily subtotals for FUND, SUM(PURCHASES). Then, the 2-year query would only need to look at 730 rows, not a 4 million. It would, however, mean adding a new row each day.