Алгоритм поиска направления, к которому относится телефонный номера

[AlexPan]

Решили оптимизировать свои скрипты и столкнулись с тем, что используемый нами алгоритм работает крайне не эфективно... Может кто чего умное подскажет.

 

Поделитесь опытом - кто как хранит коды направлений и в них производит выборку. Мы используем MySQL.

Edited June 1, 2009 by AlexPan

[GateKeeper]

        elsif NEW.destphysaddr like '%out%' then
                dst := regexp_replace(regexp_replace(NEW.lastcommandparam, '\\,.*$', ''), '\\@.*$', '');
                if length(dst) = 0 then
                        select * from directions where prefix = 'failure' into directionrow;
                        select * from zones where id = directionrow.zone_id INTO zonerow;
                        NEW.direction_id := directionrow.id;
                        NEW.eventprice := zonerow.price;
                else
                        if position('&' in dst) = 0 then
                                dsttmp := regexp_replace(dst, '^SIP\\/', '');
                        else
                                loop
                                        prefixpos := position('&' in dst);
                                        exit when prefixpos = 0;
                                        dsttmp := regexp_replace(substring(dst from 1 for (prefixpos-1)), '^SIP\\/', '');
                                        dst := substring(dst from (prefixpos+1));
                                        continue when length(dsttmp) = 3;
                                        exit when length(dsttmp) >= 7;
                                end loop;
                        end if;
                        if length(dsttmp) >= 7 and length(dsttmp) < 11 then
                                select * from directions where prefix = '495' into directionrow;
                                select * from zones where id = directionrow.zone_id INTO zonerow;
                                NEW.direction_id := directionrow.id;
                                NEW.eventprice := zonerow.price;
                        elsif length(dsttmp) < 7 then
                                select * from directions where prefix = 'failure' into directionrow;
                                select * from zones where id = directionrow.zone_id INTO zonerow;
                                NEW.direction_id := directionrow.id;
                                NEW.eventprice := zonerow.price;
                        else
                                dst := substring(dsttmp from 2);
                                counter := 1;
                                prefixnotfound := FALSE;
                                singlematchposition := 0;
                                loop
                                        dsttmp := substring(dst from 1 for counter);
                                        counter := counter+1;
                                        exit when length(dst) < counter;
                                        begin
                                                select * from directions where prefix like dsttmp || '%' into directionrow;
                                        exception
                                                when NO_DATA_FOUND then
                                                        prefixnotfound := TRUE;
                                                        exit;
                                        end;
                                        begin
                                                select * from directions where prefix = dsttmp into strict singlematch;
                                        exception
                                                when NO_DATA_FOUND then continue;
                                        end;
                                        singlematchposition := counter-1;
                                end loop;
                                if singlematchposition = 0 then
                                        select * from directions where prefix = 'unknown' into directionrow;
                                        select * from zones where id = directionrow.zone_id INTO zonerow;
                                        NEW.direction_id := directionrow.id;
                                        NEW.eventprice := zonerow.price;
                                else
                                        dsttmp := substring(dst from 1 for singlematchposition);
                                        select * from directions where prefix = dsttmp into directionrow;
                                        select * from zones where id = directionrow.zone_id INTO zonerow;
                                        NEW.direction_id := directionrow.id;
                                        NEW.eventprice := zonerow.price;
                                end if;
                        end if;
                end if;
        end if;

 

Не фонтан, и не мускль, конечно, но в качестве примера логики работы. Это только кусок триггера, выше были костыли для кривого лог-файла (т.к. там в течение нескольких месяцев менялась логика работы до неузнаваемости, а * в логах нормально писать никто не научил).

[YuryD]

Есть таблица с кодом направления, длиной кода, названиями и ценами. По номеру делается вот такая выборка стоимости

 

select xcost from $ztbl where left(code,codelen)=left('$phone',codelen) order by codelen desc limit 1

 

Защищено копирайтом от Piva, при использовании в комментариях писать - "Piva - лучший программист !"

[GateKeeper]

Кстати да, алгоритм свой пересмотрел - чернуха какая-то. Для постгреса так было бы:

select * from direction where substring($dialednumber from 1 for length(prefix)) = prefix order by length(prefix) desc limit 1

 

Пойду пеплом голову сыпать.

[GHhost]

select Location_ID,prefix_id,prefix,max(CHAR_LENGTH(prefix)) as len

from e164_prefix where '$p_number' like concat(prefix,'%')

group by prefix order by len DESC LIMIT 1;

 

мускуль.