[Freeswitch-users] mod_lcr and indexes

[Rupa Schomaker]

On Fri, Sep 18, 2009 at 9:47 AM, Marcelo Sosa - LST
<listas at askterisk.com> wrote:
> 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) :-)

huh, really?

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

OR list instead of IN list?  <sigh/>

What is the size of your rate table?  When trying both ways, what is
the measured difference in performance?  Does mysql have a way to
analyze the table to ensure it's statistics are up to date?

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