COMPARING WITH INNODB

[NITESH SINHA]

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.