Slow queries

[Eric]

One of the issues I've been struggling with lately is expensive sphinx MySQL queries due to the number of MVAs I have attached to certain model classes.  For example, if you are sphinx-indexing a list of cars, and pulling in attributes like:

- internal_accessories

- external_accessories

if internal_accessories and external_accessories live in the same table, you can end up with a quasi-cartesian join which then has to be grouped together.  I have one query which takes a while to parse because 14,000 model records result in an ungrouped result set of over 1 million records.

I've tried to avoid this by using separate indices, and making some attributes present in index A and other attributes present in index B.  That mostly works, but can lead to some inaccurate results if certain combinations of attributes are used in filters, and is awkward to maintain.

My application has a mix of updates and inserts that need to be re-indexed.  The optimization that occurs to me is to generate the MVA lists at the time a model is saved as a set of comma-delimited strings.  Then the sphinx query would be very fast as the grouping and joining would be already done on a record-by-record basis.

Wondering if anybody else has considered this approach or has an alternate solution.  I know I've read some other posts about creating temp tables to try to address this issue.

Thanks,
Eric

[Eric]

Note that if any change is made to the index, the pre-generated MVA results would need to be regenerated.  So maybe have a cap script to do that automatically after a deploy..

--> Eric

[Pat Allan]

Hi Eric

Have you looked at shifting your MVAs out into separate queries, through the :source option? I realise it's not particularly well documented, but it works like this:

has internal_accessories.value, :as => internal_values, :source => :query

There's also :ranged_query, which pages the MVA query accordingly (much like the main sql_query is). If you run ts:conf, you'll see how it impacts the source definition.

It may help cut down indexing time - at the very least, it's worth checking out.

Cheers

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/2GbVtkFs3GYJ.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.

[Eric Hansen]

Thanks Pat!
I am going to look at :source as an option.

--> Eric

[Eric Hansen]

For others who are interested, here is the relevant sphinxsearch documentation on sql_attr_multi:
http://sphinxsearch.com/docs/1.10/conf-sql-attr-multi.html

Breaking up the MVAs into separate queries may well solve this problem, looks hopeful!

--> Eric