OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
 
Memory tables and INNODB have different query plans for GROUP BY with btree?

From: Kevin Burton (burtonator.listgmail.com)
Date: Mon Oct 03 2005 - 21:18:22 CDT

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