Alex Katebi <[EMAIL PROTECTED]> wrote:
create table rtm(dest blob, mask blob, nh blob);
create index rtmi on rtm(dest);
select nh from rtm where dest = bitwise_and(addr, mask) order by mask
desc limit 1;
hex(dest) hex(mask) rowid
---------------- ------------------ -----------
01010100 FFFFFF00 1
00000000 000000000 2
01010000 FFFF0000 3
01000000 FF000000 4
04048000 FFFFC000 5
77777780 FFFFFFF0 6
So for a packet dest address 01010165, there will be four hits row
1,2,3,4 but 1 has the longest bit mask.
Every thing works except for the indexing. My custom function
bitwise_and is always called for all the rows in my table. Which
means that indexing is not being used.
How do you expect the index to be used for such a query? An index can
help quickly select rows where a particular column has a particular
value. Turning this around, an index can quickly dismiss as non-matching
any row where the column that's part of the index doesn't match the
given value, without looking at any other columns in this row.
But in your query, the value you are looking for depends on those other
columns. So you can't dismiss any row based only on fields that are part
of the index - you have to look at each row in the table.
What should I do to get around this problem.
Relational database doesn't appear to be a suitable data structure.
Essentially, you have a set of prefixes, and for a given word you want
to find its longest prefix that's in the set. For that, I'd build a
prefix tree aka trie:
http://en.wikipedia.org/wiki/Trie
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
