Thank you for your answer.
I pass a :conditions like this:
*conditions: { has_12ncs: _12nc_string }*
where *has_12ncs* is a *TEXT* field in MySQL filled with a denormalized
structure of 1 to Many relationship between Digitalassets record and 12NC
(product)
So the value in *has_12ncs* field can be *,123,456,789,*
the string passed to conditions: { has_12ncs: _12nc_string } is something
like:
",851322311100, | ,851304211100, | ,851340001000, | ,851343701000, |
,851343801000, | ,851343901000, | ,851344001000, | ,858758938790, |
,858759001790, | ,858759038790, | ,858759701790, | ,851343801020, |
,851343701020, | ,851344001020, | ,851343901020, | ,858759901740, |
,858759701740, | ,857542038000, | ,857594938000, | ,857542438000, |
,850120596000, | ,850120696000, | ,850120796000, | ,850122601020, |
,850122701000, | ,850122701010, | ,850122801000, | ,850122801020, |
,851315001000, | ,851321801000, | ,851535101040, | ,858600015000, |
,858600096000, | ,858600096010, | ,850122901010, | ,850123001000, |
,850123001020, | ,851315001010, | ,850123001010, | ,851328301000, |
,851328301010, | ,851328401010, | ,851385101080, | ,854001638000, |
,854071038080, | ,854071038090, | ,854074638070, | ,856070001080, |
,856079838070, | ,857532008400, | ,857532108400, | , ,"
That field has a FULLTEXT index.
What's the query SQL triggered against this field and values? I'm not able
to print the pure SQL.
Thanks again.
Il giorno martedì 14 gennaio 2014 10:46:14 UTC+1, Pat Allan ha scritto:
>
> Hi Yuri
>
> I don’t think I’ve come across this problem before - that’s a *really*
> slow query!
>
> Can you share your index definition with us? And are you also passing in a
> search term, or field/attribute filters (the :conditions and :with
> options)?
>
> --
> Pat
>
> On 14 Jan 2014, at 8:40 pm, [email protected] <javascript:> wrote:
>
> > Hi all,
> > for first sorry for my english.
> >
> > I've got a problem with RoR and Thinking Sphinx gem.
> >
> > I mean: everything works fine but when I try to get a search result
> > passing a lot of parameter values through Active Record and TS API my
> > application slow down and become unusable.
> >
> > I need to send queries on a MyISAM table with over 600.000 records
> >
> > There's a query field that I can match with 0 or N values
> > If I set 0 or few values the query is fast.
> > If I set 1000-2000 or more values for this field the query occurs over
> > 10 minutes.
> >
> > I'm not sure about the query slowness because the problem occurs only
> > when I try to get (for example) the total_count value.
> >
> > The query is something like:
> > Model.search match_mode: :extended, page: page, per_page: per_page,
> > :order => :updated_at, :sort_mode => :desc
> >
> > If I look in the searchd.query.log file I can find this informations
> >
> > [Tue Jan 14 09:42:08.394 2014] 774.749 sec [ext/2/attr- 64637 (0,20)]
> > [digitalasset_core,digitalasset_delta] @has_countries ,98, | ,88, |
> > ,ALL, | , , @has_12ncs ,851322311100, | ,851304211100, | ,851340001000,
> > | ,851343701000, | ,851343801000, | ,851343901000, | ,851344001000, |
> > ,858758938790, | ,858759001790, | ,858759038790, | ,858759701790, |
> > ,851343801020, | ,851343701020, | ,851344001020, | ,851343901020, |
> > ,858759901740, | ,858759701740, | ,857542038000, | ,857594938000, |
> > ,857542438000, | ,850120596000, | ,850120696000, | ,850120796000, |
> > ,850122601020, | ,850122701000, | ,850122701010, | ,850122801000, |
> > ,850122801020, | ,851315001000, | ,851321801000, | ,851535101040, |
> > ,858600015000, | ,858600096000, | ,858600096010, | ,850122901010, |
> > ,850123001000, | ,850123001020, | ,851315001010, | ,850123001010, |
> > ,851328301000, | ,851328301010, | ,851328401010, | ,851385101080, |
> > ,854001638000, | ,854071038080, | ,854071038090, | ,854074638070, |
> > ,856070001080, | ,856079838070, | ,857532008400, | ,857532108400, |
> > ,857565108200, | ,857565738010, | ,857575738010, | ,857576838010, |
> > ,857582338080, | ,857584738000, | ,857586738000, | ,857586838080, |
> > ,857588838080, | ,857597838010, | ,857599938030, | ,854060038010, |
> > ,854070038010, | ,854080038010, | ,857580000110, | ,856087638010, |
> > ,857597838020, | ,857586838020, | ,857576838020, | ,854076738020, |
> > ,857597838030, | ,857587838010, | ,856010038020, | ,856087838020, |
> > ,857500038020, | ,857500138020, | ,857500238020, | ,857500338020, |
> > ,854020038020, | ,856010048020, | ,856010058020, | ,857500348020, |
> > ,857500358020, | ,857500368020, | ,857580001100, | ,851330038000, |
> > ,851330138000, | ,851330238000, | ,851330338000, | ,851330438000, |
> > ,851330538000, | ,851330601000, | ,851330701000, | ,851330801000, |
> > ,851330901000, | ,851331101000, | ,851331201000, | ,851331301000, |
> > ,851331401000, | ,851331501000, | ,851331838000, | ,851331938000, |
> > ,851338038000, | ,851338138000, | ,851338201000, | ,851338301000, |
> > ,851338401000, | ,851338501000, | ,851345638000, | ,851345738000ÿ
> >
> > the 12NCS are the values that I want to match in order to limit the
> > resultset.
> >
> > Can anybody help me?
> > Thanks in advance.
> >
> >
> > --
> > You received this message because you are subscribed to the Google
> Groups "Thinking Sphinx" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to [email protected] <javascript:>.
> > To post to this group, send email to
> > [email protected]<javascript:>.
>
> > Visit this group at http://groups.google.com/group/thinking-sphinx.
> > For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/thinking-sphinx.
For more options, visit https://groups.google.com/groups/opt_out.
