MySQL Forums
Forum List  »  Memory Storage Engine

COMPARING WITH INNODB
Posted by: NITESH SINHA
Date: February 14, 2007 08:53AM

I m working with memory table, but some problems are occuring while selecting the rows from the table.
table structure
table name: RATELIST
planid(non unique index) int, code int, country varchar, rate decimal
table type = memory

data stored as below:
planid code country rate
1 1425 USA - Alaska .5
1 1 USA .2
2 1425 USA - Alaska .5
2 1 USA .2

query exec:
select rate,country from RATELIST where instr(142546997,code)=1 and planid =1 limit 1;
i expect the result:
rate country
.5 USA - Alaska

but the result is
rate country
.2 USA

This query is to select the rows for longest match with code and number like(142546997)

The same query when executing by changing the table to innodb, it works perfect, so why its giving wrong result by using memory table.

and one more question is that, is there any way to use indexes on code column with executing the same query.



Edited 2 time(s). Last edit at 02/17/2007 03:34AM by NITESH SINHA.

Options: ReplyQuote


Subject
Views
Written By
Posted
COMPARING WITH INNODB
5524
February 14, 2007 08:53AM
3949
February 16, 2007 03:28AM


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.