[Asterisk-Users] options for mysql query from dialplan

[Matthew Boehm]

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 at 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
>