MySQL Forums
Forum List  »  Memory Storage Engine

COMPARE WITH INNODB QUERY CHANGE
Posted by: NITESH SINHA
Date: February 17, 2007 03:34AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
COMPARE WITH INNODB QUERY CHANGE
5613
February 17, 2007 03:34AM


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.