thr3ads.net - asterisk users - [Asterisk-Users] options for mysql query from dialplan [Aug 2005]

If this information is useful, please help other people find it:
Share via:

Damon Estep

2005-Aug-18 08:41 UTC

[Asterisk-Users] options for mysql query from dialplan

I am using realtime mysql for extensions, sip, and voicemail.

Outbound call routing does not really perform well in realtime
extensions due to the high number of rows in the database (300k), so I
can not use it. It appears with my limited knowledge that the query
method is not robust enough for large databases.

Given the fact that I already have realtime and mysql configured, what
are my options for running a mysql query from the dialplan to find the
provider I want to use for outbound. 

I am not looking for a complete solution, just a hint on the best way to
query my existing mysql database from the dialplan.

I have looked at the MySQL command, and there are a lot of notes about
connection closing and other scary stuff? Does it work?

Are there other native options given the fact that realtime is
configured and in use?

The goal is to run a query against a database like this

SELECT provideralias FROM ldproviders WHERE npa = (digits 2 thru 4 of
dialed number) AND nxx = (digits 5 thru 7)

Then take the provider alias returned and
Dial(SIP/${EXTEN}@${provideralias},60).

Next step would be to add a loop for multiple providers, starting with
the lowest cost.

Any hints or comments from the pros?

TIA

Damon

Roger Gulbranson

2005-Aug-18 09:25 UTC

head link

[Asterisk-Users] options for mysql query from dialplan

On Thu, 2005-08-18 at 09:41 -0600, Damon Estep wrote:> I am using realtime mysql for extensions, sip, and voicemail.
> 
> Outbound call routing does not really perform well in realtime
> extensions due to the high number of rows in the database (300k), so I
> can not use it. It appears with my limited knowledge that the query
> method is not robust enough for large databases.
> 
> Given the fact that I already have realtime and mysql configured, what
> are my options for running a mysql query from the dialplan to find the
> provider I want to use for outbound. 
> 
> I am not looking for a complete solution, just a hint on the best way to
> query my existing mysql database from the dialplan.
> 
> I have looked at the MySQL command, and there are a lot of notes about
> connection closing and other scary stuff? Does it work?
> 
> Are there other native options given the fact that realtime is
> configured and in use?
> 
> The goal is to run a query against a database like this
> 
> SELECT provideralias FROM ldproviders WHERE npa = (digits 2 thru 4 of
> dialed number) AND nxx = (digits 5 thru 7)
> 
> Then take the provider alias returned and
> Dial(SIP/${EXTEN}@${provideralias},60).
> 
> Next step would be to add a loop for multiple providers, starting with
> the lowest cost.
> 
> Any hints or comments from the pros?
Have you added appropriate indexes to your tables?

Matthew Boehm

2005-Aug-18 09:35 UTC

head link

[Asterisk-Users] options for mysql query from dialplan

Hi Damon,
  You are basically doing EXACTLY what we are doing right now; except we 
are doing more.

We now have an AGI PHP script that does the following for every call:

- Connect to MySQL over LAN
- If the dialed number begins with 1, strip it.
- SELECT State FROM lcr_lata WHERE NPA = $dial_npa AND NXX = $dial_nxx
- Do some PHP logic to determine if Interstate vs Intrastate
- SELECT rate, address, technology, prefixes FROM lcr_rates
     LEFT JOIN lcr_carriers USING(carrierid)
     WHERE NPA = $dial_npa AND NXX = $dial_nxx
     AND carrier_active = 1 ORDER BY rate ASC;
- Loop thru results.

lcr_rates has 329,530 rows.
lcr_carriers has 8 rows.
lcr_lata has over 150,000 rows.

Everything preforms in real time.

Here is a sample query of a call that just went thru:

SELECT r.Interstate, rc.name, rc.technology, rc.address, rc.prefix FROM 
lcr_rates r LEFT JOIN lcr_carriers rc ON r.CarrierId = rc.id WHERE r.NPA 
= '254' AND r.NXX = '463' AND r.active = 1 ORDER BY r.Intrastate
ASC,
r.NPA DESC, r.NXX DESC

Query took 0.0025 sec.

I don't see how your table with 300K rows is preforming worse than ours. 
You got indexes?

To make this even better, our MySQL server is a Quad P3 500 Mhz machine.

Works great here.

-Matthew

Damon Estep wrote:> I am using realtime mysql for extensions, sip, and voicemail.
> 
> Outbound call routing does not really perform well in realtime
> extensions due to the high number of rows in the database (300k), so I
> can not use it. It appears with my limited knowledge that the query
> method is not robust enough for large databases.
> 
> Given the fact that I already have realtime and mysql configured, what
> are my options for running a mysql query from the dialplan to find the
> provider I want to use for outbound. 
> 
> I am not looking for a complete solution, just a hint on the best way to
> query my existing mysql database from the dialplan.
> 
> I have looked at the MySQL command, and there are a lot of notes about
> connection closing and other scary stuff? Does it work?
> 
> Are there other native options given the fact that realtime is
> configured and in use?
> 
> The goal is to run a query against a database like this
> 
> SELECT provideralias FROM ldproviders WHERE npa = (digits 2 thru 4 of
> dialed number) AND nxx = (digits 5 thru 7)
> 
> Then take the provider alias returned and
> Dial(SIP/${EXTEN}@${provideralias},60).
> 
> Next step would be to add a loop for multiple providers, starting with
> the lowest cost.
> 
> Any hints or comments from the pros?
> 
> TIA
> 
> Damon
> 
> 
> _______________________________________________
> Asterisk-Users mailing list
> Asterisk-Users@lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-users
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
>

Damon Estep

2005-Aug-18 10:00 UTC

head link

[Asterisk-Users] options for mysql query from dialplan

> -----Original Message-----
> From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-
> bounces@lists.digium.com] On Behalf Of Roger Gulbranson
> Sent: Thursday, August 18, 2005 10:25 AM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Cc: Roger Gulbranson
> Subject: Re: [Asterisk-Users] options for mysql query from dialplan
> 
> On Thu, 2005-08-18 at 09:41 -0600, Damon Estep wrote:
> > I am using realtime mysql for extensions, sip, and voicemail.
> >
> > Outbound call routing does not really perform well in realtime
> > extensions due to the high number of rows in the database (300k), so
I> > can not use it. It appears with my limited knowledge that the query
> > method is not robust enough for large databases.
> >
> > Given the fact that I already have realtime and mysql configured,
what> > are my options for running a mysql query from the dialplan to find
the> > provider I want to use for outbound.
> >
> > I am not looking for a complete solution, just a hint on the best
way to> > query my existing mysql database from the dialplan.
> >
> > I have looked at the MySQL command, and there are a lot of notes
about> > connection closing and other scary stuff? Does it work?
> >
> > Are there other native options given the fact that realtime is
> > configured and in use?
> >
> > The goal is to run a query against a database like this
> >
> > SELECT provideralias FROM ldproviders WHERE npa = (digits 2 thru 4
of> > dialed number) AND nxx = (digits 5 thru 7)
> >
> > Then take the provider alias returned and
> > Dial(SIP/${EXTEN}@${provideralias},60).
> >
> > Next step would be to add a loop for multiple providers, starting
with> > the lowest cost.
> >
> > Any hints or comments from the pros?
> 
> Have you added appropriate indexes to your tables?
> 
> _______________________________________________
Yep, but all the indexing in the world is not going to change the fact
that realtime extensions pulls ALL records in where the context matches,
the priority is 1, and the extension starts with an underscore!

We have over 100k extension in one table that start with an _

Damon Estep

2005-Aug-18 10:02 UTC

head link

[Asterisk-Users] options for mysql query from dialplan

Could we not do away with PHP and AGI if realtime extensions had the
ability to extend the pattern match query from _ to _ plus (n) number of
dialed digits from the left?

Damon
> -----Original Message-----
> From: asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-
> bounces@lists.digium.com] On Behalf Of Matthew Boehm
> Sent: Thursday, August 18, 2005 10:35 AM
> To: Asterisk Users Mailing List - Non-Commercial Discussion
> Subject: Re: [Asterisk-Users] options for mysql query from dialplan
> 
> Hi Damon,
>   You are basically doing EXACTLY what we are doing right now; except
we> are doing more.
> 
> We now have an AGI PHP script that does the following for every call:
> 
> - Connect to MySQL over LAN
> - If the dialed number begins with 1, strip it.
> - SELECT State FROM lcr_lata WHERE NPA = $dial_npa AND NXX = $dial_nxx
> - Do some PHP logic to determine if Interstate vs Intrastate
> - SELECT rate, address, technology, prefixes FROM lcr_rates
>      LEFT JOIN lcr_carriers USING(carrierid)
>      WHERE NPA = $dial_npa AND NXX = $dial_nxx
>      AND carrier_active = 1 ORDER BY rate ASC;
> - Loop thru results.
> 
> lcr_rates has 329,530 rows.
> lcr_carriers has 8 rows.
> lcr_lata has over 150,000 rows.
> 
> Everything preforms in real time.
> 
> Here is a sample query of a call that just went thru:
> 
> SELECT r.Interstate, rc.name, rc.technology, rc.address, rc.prefix
FROM> lcr_rates r LEFT JOIN lcr_carriers rc ON r.CarrierId = rc.id WHERE
r.NPA> = '254' AND r.NXX = '463' AND r.active = 1 ORDER BY
r.Intrastate ASC,
> r.NPA DESC, r.NXX DESC
> 
> Query took 0.0025 sec.
> 
> I don't see how your table with 300K rows is preforming worse than
ours.> You got indexes?
> 
> To make this even better, our MySQL server is a Quad P3 500 Mhz
machine.> 
> Works great here.
> 
> -Matthew
> 
> Damon Estep wrote:
> > I am using realtime mysql for extensions, sip, and voicemail.
> >
> > Outbound call routing does not really perform well in realtime
> > extensions due to the high number of rows in the database (300k), so
I> > can not use it. It appears with my limited knowledge that the query
> > method is not robust enough for large databases.
> >
> > Given the fact that I already have realtime and mysql configured,
what> > are my options for running a mysql query from the dialplan to find
the> > provider I want to use for outbound.
> >
> > I am not looking for a complete solution, just a hint on the best
way to> > query my existing mysql database from the dialplan.
> >
> > I have looked at the MySQL command, and there are a lot of notes
about> > connection closing and other scary stuff? Does it work?
> >
> > Are there other native options given the fact that realtime is
> > configured and in use?
> >
> > The goal is to run a query against a database like this
> >
> > SELECT provideralias FROM ldproviders WHERE npa = (digits 2 thru 4
of> > dialed number) AND nxx = (digits 5 thru 7)
> >
> > Then take the provider alias returned and
> > Dial(SIP/${EXTEN}@${provideralias},60).
> >
> > Next step would be to add a loop for multiple providers, starting
with> > the lowest cost.
> >
> > Any hints or comments from the pros?
> >
> > TIA
> >
> > Damon
> >
> >
> > _______________________________________________
> > Asterisk-Users mailing list
> > Asterisk-Users@lists.digium.com
> > http://lists.digium.com/mailman/listinfo/asterisk-users
> > To UNSUBSCRIBE or update options visit:
> >    http://lists.digium.com/mailman/listinfo/asterisk-users
> >
> 
> 
> _______________________________________________
> Asterisk-Users mailing list
> Asterisk-Users@lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-users
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users

Possibly Parallel Threads

Search for more reasonably related threads

asterisk users - Aug 2005 - options for mysql query from dialplan

[Asterisk-Users] options for mysql query from dialplan

[Asterisk-Users] options for mysql query from dialplan

[Asterisk-Users] options for mysql query from dialplan

[Asterisk-Users] options for mysql query from dialplan

[Asterisk-Users] options for mysql query from dialplan

Possibly Parallel Threads