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: [email protected] [mailto:[email protected]] On Behalf Of Diego Viola Sent: Tuesday, October 13, 2009 7:54 PM To: [email protected] 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 > [email protected]<mailto:[email protected]> > 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 [email protected]<mailto:[email protected]> 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 [email protected] http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
