Hello.
Please send the output of SHOW CREATE TABLE for each table. > Note no index is used. MEMORY tables usually uses HASH index which might be the source of the problem. Change the index to B-Tree and check if the query plan have changed. Kevin Burton wrote: > 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 > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
