MEMORY table seems very slow

[Chris Blackwell]

Hi,

I have a memory table specifically for doing some very quick lookups, it looks like this;

CREATE TABLE `mnocLookup` (
`mnocId` int(10) unsigned NOT NULL,
`cns` char(15) NOT NULL,
PRIMARY KEY (`mnocId`),
KEY `idx_cns` (`cns`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

On my workstation (5.1.42 64b) i can query this very quickly. for example;


mysql> select benchmark(1000000, (
-> SELECT SQL_NO_CACHE COUNT(mnocId)
-> FROM mnocLookup
-> WHERE cns LIKE concat(SUBSTRING('1234567988798', 1, 7),'%'))
-> ) as x;
+---+
| x |
+---+
| 0 |
+---+
1 row in set (0.02 sec)

However on our production server (5.0.77 64b) the same benchmark takes 5-10 times longer.

I've checked the configs and can't find anything out of whack, so i'm wondering if there were some big improvements in the Memory storage engine between 5.0 and 5.1?

Cheers, Chris