Hi,
maybe you create a function which figures out the count of equal numbers from the left. Let it for example be called: " get_equal_numbers(col_number,my_number) " See here fore documentation: CREATE FUNCTION examples
Then you may create a query like this:
SELECT col_number,rate,get_equal_numbers(col_number,my_number) AS equals
FROM destinations
WHERE equals >= 1
ORDER BY equals DESC
I'm not sure if this performes well.
The choice I normally would take is too read the whole table into an array and use a custom search function, which does the same as above.
Both solutions are not that efficient - especially when you have a lot of lookups to accomplish.
For speed and improving search effiency, I probably would build a hash like this(example in ruby-code):
#!/usr/bin/ruby
def get_routing_target(number)
# number is a string
routing_table = [
"001" => "data1",
"0012 => "data2",
"00129" => "data3" ]
for current_length in number.length .. 1
if routing_table[number[0,current_length]] then
return routing_table[number[0,current_length]
end
end
end
The last idea does not allow to have multiple routing targets for the same number. So this needs a bit adjustment to enable that("data" may be implemented as an array of multiple routing targets).
In general I think this may be a performance critical issue, which may cause very high load - depending on the number of requests that will be processed - on your server(DB or application server) if not designed well. So maybe it is better to have a program running as daemon, keeping the complete routing table in memory and reloading from time to time instead of reading it for every request.
UPDATE-1:
I tried it and created some test data(100k Records, with index on the number) and a mysql function:
DROP FUNCTION IF EXISTS equal_chars_from_left;
DELIMITER $$
CREATE FUNCTION equal_chars_from_left(wanted TEXT, current_field TEXT)
RETURNS INT
BEGIN
DECLARE current_length INT;
SET current_length = LENGTH(wanted);
count_loop: LOOP
IF SUBSTRING(current_field,1,current_length) = SUBSTRING(wanted,1,current_length) THEN
LEAVE count_loop;
END IF;
SET current_length = current_length - 1;
IF current_length = 0 THEN
LEAVE count_loop;
END IF;
END LOOP;
RETURN current_length;
END;
$$
DELIMITER ;
The performance is as bad as expected.
mysql> select destination,rate,equal_chars_from_left("1212313",destination) as equals from test order by equals desc limit 10;
+----------------+------+--------+
| destination | rate | equals |
+----------------+------+--------+
| 1212313893101 | 0.97 | 7 |
| 12123105591 | 0.09 | 6 |
| 12123 | 0.98 | 5 |
| 12123235501917 | 0.07 | 5 |
| 121283339945 | 0.19 | 4 |
| 121243075 | 0.53 | 4 |
| 121251 | 0.69 | 4 |
| 1212626040 | 0.23 | 4 |
| 1212914454156 | 0.72 | 4 |
| 121251042914 | 0.46 | 4 |
+----------------+------+--------+
10 rows in set (6,35 sec)
UPDATE 2:
This is a bit faster:
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '1212313%' UNION
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '121231%' UNION
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '12123%' UNION
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '1212%' UNION
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '121%' UNION
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '12%' UNION
SELECT destination,equal_chars_from_left('1212313',destination) as equals from test where destination like '1%'
ORDER BY equals DESC LIMIT 10;
+----------------+--------+
| destination | equals |
+----------------+--------+
| 1212313893101 | 7 |
| 12123105591 | 6 |
| 12123 | 5 |
| 12123235501917 | 5 |
| 12120247005 | 4 |
| 121283339945 | 4 |
| 12125241424986 | 4 |
| 1212426494209 | 4 |
| 1212116831 | 4 |
| 121284268 | 4 |
+----------------+--------+
10 rows in set (2,10 sec)