longest matching prefix

Question

In MySQL to match '12684041234' to the longest prefix you would do

SELECT num_prefix
FROM nums
WHERE '12684041234' LIKE CONCAT(num_prefix, '%')
AND LENGTH(num_prefix) = (
    SELECT MAX(LENGTH(num_prefix))
    FROM nums
    WHERE '12684041234' LIKE CONCAT(num_prefix, '%')
)

Table nums has a column named num_prefix with prefix values.

How can I do it in hive ?

I think that your current SQL query is not the most efficient way to do it as it will require a full table scan. Is performance going to be an issue? — Mark Byers, Nov 06 '10 at 07:50
@Mark Byers: Performance is not an issue. It's a batch job, the table of the prefixes is not big (few hundreds) , and I can cache the results if I need to — Eyal, Nov 06 '10 at 15:10

score 8 · Accepted Answer · edited Oct 12 '11 at 09:05

8

This is how I do it in MySQL:

SELECT num_prefix FROM nums
  WHERE '12684041234' LIKE CONCAT(num_prefix,'%')
  ORDER BY num_prefix DESC
  LIMIT 1
;

This will give the longest prefix (ORDER BY .. DESC) and only one row (LIMIT 1).

edited Oct 12 '11 at 09:05

Jordan Running

102,619
17
182
182

answered Apr 13 '11 at 13:11

x0d

96
1
2

2

This will give extremely poor performance. That condition can never use an index on num_prefix, therefore this query will always need a full table scan. It's a little more effort but you can search for WHERE num_prefix IN (12684041234,1268404123,126840412,12684041,1268404,126840,12684,1268,126,12,1). – steveayre Aug 02 '13 at 08:00
1

Although I haven't verified it myself, reportedly the query will be roughly 22x faster if you quote the numbers. WHERE num_prefix IN ('12684041234','1268404123','126840412','12684041','1268404','126840','12684','1268','126','12','1') Source: http://wiki.freeswitch.org/wiki/Mod_lcr – steveayre Aug 02 '13 at 08:04

longest matching prefix

1 Answers1

Linked