I fully agree that direct matching is much faster then pattern matching in SQL.

One of my clients had same problem, he had around 12 million number prefixes in a table and during each call an AGI script use to query that table to find longest prefix match, but this use to take like 3-5 seconds even with indexed columns. So after a lots of R & D we come up with following logic,

1. We break the destination number length-wise, e.g. suppose number is 923344224088 then length chunks would be,

923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9

2. Then use SQL LIKE function in WHERE clause (you can also use SQL OR function if your DBMS doesn't support SQL LIKE function), and pass all these chunks to it, e.g.

WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)

3. Lastly we ORDER the result by prefix length, e.g.

ORDER BY LENGTH(prefix) DESC LIMIT 1

4. The complete query will be,

SELECT * FROM prefixes
WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)
ORDER BY LENGTH(prefix) DESC LIMIT 1

Now the query takes less then 150 ms to execute. :-)

Here is an STL method that can generate this query, i am sure you can convert it to any programming language of your choice easily.

=========================================================
std::string GetQuery(std::string destination) {
        std::string query = "SELECT * FROM prefixes WHERE prefix LIKE ('" + destination;

        for(int i=1; i<destination.length(); i++) {
                query += "','" + destination.substr(0, (i * -1));

        }

        query += "') ORDER BY LENGTH(prefix) DESC LIMIT 1";
        return query;
}
=========================================================

I am pretty sure this query is 100% ANSI SQL compatible (http://en.wikipedia.org/wiki/SQL).

Thank you.


On Wed, Oct 14, 2009 at 10:15 AM, Michael Giagnocavo <mgg@giagnocavo.net> wrote:

In our testing with SQL Server, we found that executing several queries for direct matches yielded far better performance than one query trying to check prefixes. (The column was also part of the clustered index, but AFAIK MySQL doesn’t support defining your own clustered indexes; you get the PK always.)

 

-Michael

 

From: freeswitch-users-bounces@lists.freeswitch.org [mailto:freeswitch-users-bounces@lists.freeswitch.org] On Behalf Of Diego Viola
Sent: Tuesday, October 13, 2009 7:54 PM
To: freeswitch-users@lists.freeswitch.org
Subject: Re: [Freeswitch-users] Some help with my post-paid billing project

 

Wrong question.

Is there a way to compare numbers with prefixes without using the prefix module?

Diego

On Wed, Oct 14, 2009 at 1:36 AM, Diego Viola <diego.viola@gmail.com> wrote:

I'm using MySQL now but I will try PostgreSQL with the prefix module, is there a way to do that without the prefix module and with regular SQL?

Any examples?

Diego

 

On Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik <grevenx@me.com> wrote:

What database are you using?
You could do this with regular SQL, but it would by a costly operation,
for PostgreSQL we're using the prefix module: http://pgfoundry.org/projects/prefix/

You can then match the closest prefix by using something like
"WHERE myprefix_col @> caller_destination_number ORDER BY LENGTH
(myprefix_col::text) LIMIT 1;"


Best regards,
Even André



On 13. okt. 2009, at 23.53, Diego Viola wrote:

> Hello,
>
> I'm trying to write a post-paid billing script, I have the CDR on my
> database and also a "rates" table, the CDR contains fields like
> caller_destination_number, variable_duration, etc. and the rates
> table contains: destination, prefix, rate (cost).
>
> The problem is that I can't just strip the destination number to
> take the prefix from it because I have to deal with destination
> numbers from different countries and they all have different prefix
> lengths... so I need to find another way to take the prefix from the
> destination number.
>
> Any ideas how to do this?
>
> Thanks,
>
> Diego
>

> _______________________________________________
> FreeSWITCH-users mailing list
> FreeSWITCH-users@lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-
> users
> http://www.freeswitch.org


_______________________________________________
FreeSWITCH-users mailing list
FreeSWITCH-users@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
http://www.freeswitch.org

 

 


_______________________________________________
FreeSWITCH-users mailing list
FreeSWITCH-users@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
http://www.freeswitch.org




--
________________________________________________________
|                                                                                                |
| FATAL ERROR                                                           --- O X |
|_______________________________________________________|
|                        You have moved the mouse.                            |
| Windows must be restarted for the changes to take effect.   |
|                                        <OK>                                              |
####################################/


Muhammad Shahzad
-----------------------------------
CISCO Rich Media Communication Specialist (CRMCS)
CISCO Certified Network Associate (CCNA)
Cell: +92 334 422 40 88
MSN: shari_786pk@hotmail.com
Email: shaheryarkh@googlemail.com