[Asterisk-Users] mysql phone number pattern match query

[Simone Cittadini]

>
>
>  
>
> I am not a mySQL expert (obviously), my limited SQL experience is with 
> MS SQL where stored procedures and views are an option.
>
>  
>
> This is with mySQL 4.x, so no views.
>
I'm no an expert too, but even if the algorithm is right and seems to 
bring some optimization I think mysql way of do things can't leverage 
such a method

> Select dialpattern from rates where left 5 match left 5 of dst
>
this is a select of a substring, I don't think mysql can index a 
substring, so the query will be redone completely every time

> Order by length of dialpattern, descending
>
I'm pretty sure mysql isn't so good at sorting, you're wasting a little 
more time

> Compare dialpattern to the first x number of digits from dst where x = 
> the length of dial pattern
>
here you have another substring

> The first match (when ordered by length descending) is the correct 
> result (longest match)
>
>  
>
Now of course the performance issue is relative since we are searching 
between two little strings and not for some book with 'asterisk' and 
'future' in the title on amazon.
Since performance isn't probably an issue I suggest a simple

price = None
for (i=1, i++, i<len(dialstring))
    price = select price from rates where prefix = 
dialstring[0:len(dialstring)-i]
    if price != None break
if price == None we don't know how to bill this call
else do stuff

you have an O(len(dialstring)) search but the code is simple and cpus 
are fast

If you know your system will never call numbers shorter than m you can 
substitue len(dialstring) with len(dialstring)-m

If performance is an issue maybe (never tried myself) you can split the 
prefixes table in one table for the first 4 chars, like

0011 America1
0012 America2
...
0020 Egypt
...
0086 China
...


and one table for every destination with the remaining part of the code, 
so you
first do a select on the first 4 chars of the dialed number, you know 
you'll always have one and only one match.
the match is the name of the table where to do the O(n) search, but now 
n is even smaller and there is also a smaller  number of rows to search 
from.
(too bad international prefixes aren't all of the same length, so the 
numbers in the tables have less sense and you probably need a little 
more complex billing application)

If you need to investigate what is the better query use EXPLAIN in front 
of them, and look at how mysql will do the query, what index uses and 
how many lines will it go through ....