COMPARE WITH INNODB QUERY CHANGE

[NITESH SINHA]

SORRY FOR THE WRONG QUERY, I HAVE CORRECTED THE QUERY
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.

There is no need to include order by clause because codes are already arranged in a order we want like

14253
1425
1



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