Range querygreenspun.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?
-- Phil (email@example.com), July 02, 2003