Index is not used for MyISAM table for SELECT+GROUP BY

[Nilnandan Joshi]

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.

[Nilnandan Joshi (nilnandan)]

Upstream bug
http://bugs.mysql.com/bug.php?id=66967

[Valerii Kravchuk (valerii-kravchuk)]

Verified with upstream 5.5.27.

[Valerii Kravchuk (valerii-kravchuk)]

Verified with upstream 5.1.62 also. This is not a recent regression.

[Valerii Kravchuk (valerii-kravchuk)]

Some more evidence (for the table with 1024 rows only):

mysql> EXPLAIN Select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,c
ol12,col13,col14,col15,sum(col16) Total From tab_MYISAM
    -> Group By col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,c
ol13,col14,col15;
+----+-------------+------------+------+---------------+------+---------+------+
------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
 rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+
------+---------------------------------+
| 1 | SIMPLE | tab_MYISAM | ALL | NULL | NULL | NULL | NULL |
 1024 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+
------+---------------------------------+
1 row in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.03 sec)

mysql> Select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col
13,col14,col15,sum(col16) Total From tab_MYISAM
    -> Group By col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,c
ol13,col14,col15;
+------+------+------+------+-------+------+------+---------+------+---------+--
-----+-------+-------+-------+-------+--------+
| col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | c
ol11 | col12 | col13 | col14 | col15 | Total |
+------+------+------+------+-------+------+------+---------+------+---------+--
-----+-------+-------+-------+-------+--------+
| Y | test | key | test | 12334 | Y | N | testing | Y | testing | Y
     | N | 12345 | Y | N | 125952 |
+------+------+------+------+-------+------+------+---------+------+---------+--
-----+-------+-------+-------+-------+--------+
1 row in set (0.52 sec)

mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1024 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1 |
| Handler_read_rnd_next | 1027 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1023 |
| Handler_write | 1 |
+----------------------------+-------+
15 rows in set (0.45 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN Select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,c
ol12,col13,col14,col15,sum(col16) Total From tab_MYISAM force index(Multi_Col_In
dex)
    -> Group By col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,c
ol13,col14,col15;
+----+-------------+------------+-------+---------------+-----------------+-----
----+------+------+-------+
| id | select_type | table | type | possible_keys | key ...

[Stewart Smith (stewart)]

http://bugs.mysql.com/bug.php?id=35334

is current upstream bug, this has been around since 5.0 it seems.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2383