MySQL Forums
Forum List  »  Memory Storage Engine

MEMORY table seems very slow
Posted by: Chris Blackwell
Date: June 30, 2011 05:44AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MEMORY table seems very slow
8351
June 30, 2011 05:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.