MySQL Forums
Forum List  »  Memory Storage Engine

INNER JOIN between MyISAM and MEMORY STORAGE ENGINE
Posted by: M Karim
Date: June 23, 2006 05:12AM

I have question concerning Storage Engine. I have installed MySQL 5.0. According to the MySQL manual, it says that Memory Storage Engine "Extremly fast memory based storage engine that uses hash indices. Recommended storage engine for temporary data"; therefore, I used memory storage engine. But I got a long performance time, when I assigned a query. The query is:

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.item = TABLE2.item WHERE TABLE1.text LIKE "%test%";

TABLE1 is using MyISAM Storage Engine. It has 2 columns. First column "item" INTEGER type and INDEX. 2nd column "text" TEXT type and INDEX. It has 200000 records.

TABLE2 is using MEMORY STORAG ENGINE. It has one column "item" INTEGER type and INDEX. It has 9000 records.

It takes about 18 seconds. but when I change the STORAGE ENGINE of TABLE2 to MyISAM, it takes only 0.9 sec !!!!!

Any help will be great.

Thanks in advance,

Options: ReplyQuote


Subject
Views
Written By
Posted
INNER JOIN between MyISAM and MEMORY STORAGE ENGINE
7213
June 23, 2006 05:12AM


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.