Re: Adding index kills query
Posted by:
Rick James
Date: April 27, 2014 09:00PM
There are several things that "explain" what you are seeing:
* InnoDB is 2x-3x bulkier than MyISAM, hence a table scan (not using any index) has more I/O to do to get the same number of rows. (Look at SHOW TABLE STATUS to see if it is 2x, 3x, or some other ratio.)
* If you did not run each query twice, you could be getting some confusion due to caching: Run one query, it fetches some data into cache; run the next query, it may be using the same data, thereby avoiding some I/O.
* Is this the index you are turning on/off?
key idxCompBrand (iCompanyID, iBrandID)
I guess the optimizer is mistakenly using that index on the presumption that ordering the fetches by the GROUP BY makes more sense than having to do a sort. The optimizer can't always guess right -- it partially depends on whether the data _happens_ to be ordered by iCompanyID or not.
Look for the syntax that lets you apply the IGNORE INDEX to the GROUP BY.
* The cliff you are falling over may be due to trying to do GROUP BY by keeping a hash of values in RAM. If you have a recent enough version of MySQL, do EXPLAIN FORMAT=JSON SELECT ....
* (more on the cliffs) Your buffer_pool_size was 112MB; is it still? What is the value of key_buffer_size -- that is important to MyISAM index usage?
* In the case of MyISAM (only!) the following would speed up _this_ query:
ALTER TABLE Trans_smp_micro ORDER BY iCompanyID;
* With 8GB of RAM, you can afford more than 112MB for the buffer pool. But increasing it will only move the cliff, not avoid it.
Sorry, I don't have a simple answer.
Consider Hadoop & PIG for massive analytics like this.