Range query

greenspun.com : LUSENET : DBAzine : One Thread


I have a problem with a very slow query. I have a million row table which contains a series of phone numbers and phone number prefixes stored as text strings. Given a phone number, I then want to find an exact match in this table, or the longest prefix match. For example:

Row Phone number 1 0123456789 2 0123 3 0

If the phone number I have is 0123456789 I should match row 1, if it is 0123545454, then I should match row 2 and if it is 01333333 then I should row 3.

To help with this query, I have created a range table which would look something like the following:

Row lower_range Upper_range Original_row 1 0 0123 3 2 0123 0123456789 2 3 0123456789 012345678: 1 4 012345678: 0124 2 5 0124 1 3

I can then query this table as follows: select * from range_table where phonenumber >= lower_range and phonenumber < upper_range;

However, even with an index on the range table, this is still a relatively slow query and I have to perform this serveral million times a day. Does anyone have a better stragegy for this sort of lookup?

Thanks, Phil

-- Phil (drphil@ntlworld.com), July 02, 2003

Moderation questions? read the FAQ