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 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
___
--Bandwidth and Colocation provided by Easynews.com --
Asterisk-Users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
