Re: Adding index kills query
Posted by:
Rick James
Date: April 25, 2014 10:55AM
> select iCompanyID, count(fPurchaseAmt) FROM Trans_smp GROUP BY iCompanyID;
COUNT(fPurchaseAmt) does not make sense unless you need to count just those that are NOT NULL. Otherwise simply do COUNT(*), in which case, INDEX(iCompanyID) would be optimal. It sould do all the work inside the index, in which case, the EXPLAIN would say "Using index".
> select iCompanyID, SUM(fPurchaseAmt) FROM Trans_smp GROUP BY iCompanyID;
would probably benefit from the compound index
INDEX(iCompanyID, fPurchaseAmt)
because it could do all the work inside the index. The EXPLAIN would say "Using index".
Øystein, why do you say that FLOAT in an index is 'slow'?
> 8 GB of RAM
yet only 100MB of buffer_pool?
Of course, if the index is not yet fetched into the buffer_pool, the size does not matter. If the index is bigger than 100MB, one query will effectively purge the cache, making subsequent queries slow until the cache is re-primed.
> , key idxComp (iCompanyID)
> , key idxCompPurchAmt (iCompanyID, fPurchaseAmt)
The former is redundant, DROP it.
Furthermore, notice in
> 1 SIMPLE Trans_smp index NULL idxComp 9 NULL 15272017
that it wanted to use that index. It is very inefficient to use that index -- it has to bounce between the index and the data.
So, simply DROPping that index may be the solution.
Keep in mind that FLOAT has about 7 significant digits, so a "purchase amt" near a million dollars will lose precision of several cents. As Peter says, consider DECIMAL instead.
> ignore index (idxCompBrand)
> ignore index (idxComp)
> ignore index (idxCompPurchAmt)
> id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Trans_smp ALL NULL NULL NULL NULL 15272017 Using temporary; Using filesort
You said to ignore all the indexes; it obeyed. And it scanned the entire table to get the answer. That required reading all ~15272017 rows from disk -- slow because of I/O.
This is InnoDB, yet you have no explicit PRIMARY KEY. This is a no-no.