On Fri, Sep 18, 2009 at 9:47 AM, Marcelo Sosa - LST <[email protected]> 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" <[email protected]> > To: <[email protected]> > 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 >> <[email protected]> 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 >>> [email protected] >>> 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 >> [email protected] >> 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 > [email protected] > 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 [email protected] http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
