MySQL Bugs: #66967: Index is not used for MyISAM table with SELECT+GROUP BY

Bug #66967	Index is not used for MyISAM table with SELECT+GROUP BY
Submitted:	26 Sep 2012 8:17	Modified:	27 Sep 2012 11:37
Reporter:	Nilnandan Joshi	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5.27	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	MySQL 5.5.27

Description:
Two similar tables of InnoDB and MyISAM. Both tables contain the same type of indexes, structure and data every thing similar. one multicolumn index over 15 columns on each table for Select + Group By 

Select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,sum(col16) Total From table
Group By col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15

When I do EXPLAIN of the above query, InnoDB table uses multicolumn index. But the query over MyIsam table is not using the index. When I use FORCE INDEX for MyIsam, it worked. 

I ran the query several times on table and measure time but query with FORCE INDEX is faster than normal one. 

How to repeat:
Please check attached test case.

How to repeat

Attachment: test_case (application/octet-stream, text), 5.49 KiB.

mysqlslap output for timings of select query (with and without force index)

Attachment: mysqlslap_output (application/octet-stream, text), 1.22 KiB.

Seems issue related to optimizer

Thank you for the bug report. Please check for duplicate with http://bugs.mysql.com/bug.php?id=35334 . Thanks.

After analysis, we found a work around for the above

including the col16 is also as a part of multicolumn index solved the above problem.