How does MySQL decide if it uses an index for GROUP BY?

Question

I have a simple table

stock_ledger_id INT(10) (Primary) piece_to_bin_id INT(10) quantity INT(11) create_datetime TIMESTAMP ... and a few VARCHARs 

with some simple indexes

 Key_name Cardinality PRIMARY 1510443 piece_to_bin_id 100696 

This fairly simple query takes about 8 seconds:

 SELECT piece_to_bin_id, SUM(quantity), MAX(create_datetime) FROM stock_ledger GROUP BY piece_to_bin_id 

Here's EXPLAIN:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE stock_ledger ALL NULL NULL NULL NULL 1512976 Using temporary; Using filesort 

I found that I can shift it to about 0.5 seconds by forcing the index:

 SELECT piece_to_bin_id, SUM(quantity), MAX(create_datetime) FROM stock_ledger FORCE INDEX (piece_to_bin_id) GROUP BY piece_to_bin_id 

Then EXPLAIN looks like this:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE stock_ledger index NULL piece_to_bin_id 4 NULL 1512976 

I am using MySQL 5.1.41, the MyISAM table and before I ran ANALYZE TABLE.

So, I'm stuck in "MySQL was wrong again, just forcing the index" or is there a real reason MySQL uses full table scanning? Maybe I can fix it?

Answer 1

In any case, the query requires a full table scan, it may be that mysql is trying to avoid an additional transition from the key value in the row too. The request may significantly improve the efficiency of the combined (________________ piece po__po_opisaniya_po_tablitsa) or index (_____________________________________ po_o_o_o_o_o_o_o_o_o_o_o_o_o_o_u_o_o_o_o_o_o_o_o_o_o_o_o_o_o piece) number. The latter will become a coverage index.

UPD

It seems that the result is 16 times faster from the data distribution in your case (perhaps many neighboring rows with the same piece_to_bin_id , sorted by create_datetime ). MyISAM apparently uses query indexes, which reduce the number of result rows, since using them involves random disk I / O.

I never paid attention to this, but my current tests in a table of 10K rows show that MyISAM doesn't even use an index to sort the query, for example:

 SELECT indexed_field, another_field FROM a_table ORDER BY indexed_field; 

Even if indexed_field is the primary key.