[Freeswitch-users] mod_lcr and indexes

[Marcelo Sosa - LST]

Hello,

I know that mysql is dumb, but i´m postgresql-fobic :) (i was happy using 
pgsql, until i found a bug in the restore of backups, that makes the backup 
unable to be restored, very bad day) :-)
Anyway, I was refering to the change from "digits IN (a list of digits)" to 
"(digits='xx' OR digits='xxx')", not the quote_in_list option that i found 
when i was touching a bit of code.
For the list-archives then, mysql may prefer a different query format to 
speed up lcr matches. I´ll test with postgres and check it has any 
differences by using one method or another, if not may be we can change the 
code so it uses the fastest way for mysql.

Regards,
Marcelo Sosa

----- Original Message ----- 
From: "Rupa Schomaker" <rupa at rupa.com>
To: <freeswitch-users at lists.freeswitch.org>
Sent: Friday, September 18, 2009 11:07 AM
Subject: Re: [Freeswitch-users] mod_lcr and indexes


> Hmm....  This is because mysql is "dumb" :(  Anyway, if you wanted
> quoted digits, there is an option to enable that in the mod_lcr config
> file.
>
> http://wiki.freeswitch.org/wiki/Mod_lcr#Advanced_Usage
>
> Specifically, look at the parameter: quote_in_list
>
> The most efficient way (that I know of) to use mod_lcr is to use
> postgresql and the prefix postgres module which uses a custom datatype
> and a GIST index for the prefix column.
>
> On Thu, Sep 17, 2009 at 9:24 PM, Marcelo Sosa - LST
> <listas at askterisk.com> wrote:
>> Hello all,
>>
>> This is my first message on the list, i´m pretty new to FS.
>> I was playing a bit with mod_lcr and found that the sql query for 
>> fetching
>> the lowest rate can be changed to a better use of indexes, at least on
>> mysql. Anyone can do some test using other DBs?
>>
>> The change i've made was simple, the original query was something about 
>> "...
>> AND digits IN (12345, 1234, 123, 12, 1) ..." and using EXPLAIN i saw that 
>> it
>> was using carrier_id as key for the biggest table and not digits. I've
>> changed the code so the query is " AND (digits='12345' OR digits='1234' 
>> OR
>> digits='123' OR digits='12' OR digits='1') " and mysql uses the index 
>> from
>> the digits row, reducing the returned resultset of the subquery from all 
>> the
>> digits from a carrier to the number of "OR" in the query (in my case, 
>> from
>> 19850+ to 14).
>>
>> Anyone think that this may be a nice change? or it is just a bad use of
>> indexes by mysql?
>>
>> Regards,
>> Marcelo Sosa
>> _______________________________________________
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>
>
>
> -- 
> -Rupa
>
> _______________________________________________
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>