Memory tables and INNODB have different query plans for GROUP BY with btree?

[Kevin Burton]

I was benchmarking a few of my queries tonight and I noticed that two  
queries had different query plans based on table type.

Here's the "broken" query:
mysql> EXPLAIN SELECT * FROM FOO_LINK_MEMORY_TEST GROUP BY  
TARGET_NODE_ID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: FOO_LINK_MEMORY_TEST
         type: index
possible_keys: NULL
          key: TEST
      key_len: 18
          ref: NULL
         rows: 1000
        Extra:
1 row in set (0.00 sec)

Note no index is used.


mysql> EXPLAIN SELECT * FROM FOO_LINK_INNODB_TEST GROUP BY  
TARGET_NODE_ID\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: FOO_LINK_INNODB_TEST
         type: index
possible_keys: NULL
          key: TARGET_NODE_ID
      key_len: 9
          ref: NULL
         rows: 1011
        Extra:
1 row in set (0.00 sec)

...

and here it uses TARGET_NODE_ID. The only difference is that I  
created an INNODB table and inserted the columns in the memory table  
into the INNODB table.

I'm trying to follow the instructions here:

http://dev.mysql.com/doc/mysql/en/loose-index-scan.html

To get decent GROUP BY performance.  Is this a bug?  Is there a  
workaround?

Kevin A. Burton, Location - San Francisco, CA
      AIM/YIM - sfburtonator,  Web - http://www.feedblog.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04