I fully agree that direct matching is much faster then pattern matching in SQL.<br><br>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,<br>
<br>1. We break the destination number length-wise, e.g. suppose number is 923344224088 then length chunks would be,<br><br>923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9<br>
<br>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.<br><br> WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)<br>
<br>3. Lastly we ORDER the result by prefix length, e.g.<br><br>ORDER BY LENGTH(prefix) DESC LIMIT 1<br><br>4. The complete query will be,<br><br>SELECT * FROM prefixes <br>WHERE prefix LIKE (923344224088, 92334422408, 9233442240, 923344224, 92334422, 9233442, 923344, 92334, 9233, 923, 92, 9)<br>
ORDER BY LENGTH(prefix) DESC LIMIT 1<br>
<br> Now the query takes less then 150 ms to execute. :-)<br><br>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.<br><br>=========================================================<br>
<i>std::string GetQuery(std::string destination) {<br> std::string query = "SELECT * FROM prefixes WHERE prefix LIKE ('" + destination;<br><br> for(int i=1; i<destination.length(); i++) {<br>
query += "','" + destination.substr(0, (i * -1));<br><br> }<br><br> query += "') ORDER BY LENGTH(prefix) DESC LIMIT 1";<br> return query;<br>}<br></i>=========================================================<br>
<br>I am pretty sure this query is 100% ANSI SQL compatible (<a href="http://en.wikipedia.org/wiki/SQL">http://en.wikipedia.org/wiki/SQL</a>).<br><br>Thank you.<br><br><br><div class="gmail_quote">On Wed, Oct 14, 2009 at 10:15 AM, Michael Giagnocavo <span dir="ltr"><<a href="mailto:mgg@giagnocavo.net">mgg@giagnocavo.net</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="EN-US">
<div>
<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">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.)</span></p>
<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>
<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);">-Michael</span></p>
<p class="MsoNormal"><span style="font-size: 11pt; color: rgb(31, 73, 125);"> </span></p>
<div style="border-style: solid none none; border-color: rgb(181, 196, 223) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3pt 0in 0in;">
<p class="MsoNormal"><b><span style="font-size: 10pt;">From:</span></b><span style="font-size: 10pt;"> <a href="mailto:freeswitch-users-bounces@lists.freeswitch.org" target="_blank">freeswitch-users-bounces@lists.freeswitch.org</a>
[mailto:<a href="mailto:freeswitch-users-bounces@lists.freeswitch.org" target="_blank">freeswitch-users-bounces@lists.freeswitch.org</a>] <b>On Behalf Of </b>Diego
Viola<br>
<b>Sent:</b> Tuesday, October 13, 2009 7:54 PM<br>
<b>To:</b> <a href="mailto:freeswitch-users@lists.freeswitch.org" target="_blank">freeswitch-users@lists.freeswitch.org</a><br>
<b>Subject:</b> Re: [Freeswitch-users] Some help with my post-paid billing
project</span></p>
</div><div><div></div><div class="h5">
<p class="MsoNormal"> </p>
<p class="MsoNormal" style="margin-bottom: 12pt;">Wrong question.<br>
<br>
Is there a way to compare numbers with prefixes without using the prefix
module?<br>
<br>
Diego</p>
<div>
<p class="MsoNormal">On Wed, Oct 14, 2009 at 1:36 AM, Diego Viola <<a href="mailto:diego.viola@gmail.com" target="_blank">diego.viola@gmail.com</a>> wrote:</p>
<p class="MsoNormal">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?<br>
<br>
Any examples?<br>
<span style="color: rgb(136, 136, 136);"><br>
Diego</span></p>
<div>
<div>
<p class="MsoNormal" style="margin-bottom: 12pt;"> </p>
<div>
<p class="MsoNormal">On Tue, Oct 13, 2009 at 10:45 PM, Even André Fiskvik <<a href="mailto:grevenx@me.com" target="_blank">grevenx@me.com</a>> wrote:</p>
<p class="MsoNormal">What database are you using?<br>
You could do this with regular SQL, but it would by a costly operation,<br>
for PostgreSQL we're using the prefix module: <a href="http://pgfoundry.org/projects/prefix/" target="_blank">http://pgfoundry.org/projects/prefix/</a><br>
<br>
You can then match the closest prefix by using something like<br>
"WHERE myprefix_col @> caller_destination_number ORDER BY LENGTH<br>
(myprefix_col::text) LIMIT 1;"<br>
<br>
<br>
Best regards,<br>
Even André</p>
<div>
<div>
<p class="MsoNormal"><br>
<br>
On 13. okt. 2009, at 23.53, Diego Viola wrote:<br>
<br>
> Hello,<br>
><br>
> I'm trying to write a post-paid billing script, I have the CDR on my<br>
> database and also a "rates" table, the CDR contains fields like<br>
> caller_destination_number, variable_duration, etc. and the rates<br>
> table contains: destination, prefix, rate (cost).<br>
><br>
> The problem is that I can't just strip the destination number to<br>
> take the prefix from it because I have to deal with destination<br>
> numbers from different countries and they all have different prefix<br>
> lengths... so I need to find another way to take the prefix from the<br>
> destination number.<br>
><br>
> Any ideas how to do this?<br>
><br>
> Thanks,<br>
><br>
> Diego<br>
></p>
</div>
</div>
<p class="MsoNormal">> _______________________________________________<br>
> FreeSWITCH-users mailing list<br>
> <a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
> <a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
> UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-</a><br>
> users<br>
> <a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br>
<br>
_______________________________________________<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a></p>
</div>
<p class="MsoNormal"> </p>
</div>
</div>
</div>
<p class="MsoNormal"> </p>
</div></div></div>
</div>
<br>_______________________________________________<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br>________________________________________________________<br>| |<br>| FATAL ERROR --- O X |<br>
|_______________________________________________________|<br>| You have moved the mouse. |<br>| Windows must be restarted for the changes to take effect. |<br>| <OK> |<br>
####################################/<br><br><br>Muhammad Shahzad<br>-----------------------------------<br>CISCO Rich Media Communication Specialist (CRMCS)<br>CISCO Certified Network Associate (CCNA)<br>Cell: +92 334 422 40 88<br>
MSN: <a href="mailto:shari_786pk@hotmail.com">shari_786pk@hotmail.com</a><br>Email: <a href="mailto:shaheryarkh@googlemail.com">shaheryarkh@googlemail.com</a><br>