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 On Jul 4, 2012, at 9:26 AM, Eric Hansen wrote: > Thanks Pat! > I am going to look at :source as an option. > > --> Eric > > On Jul 4, 2012, at 8:27 AM, Pat Allan wrote: > >> 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 >> >> On 02/07/2012, at 11:17 PM, Eric wrote: >> >>> 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 >>> >>> On Monday, July 2, 2012 2:13:13 PM UTC-7, Eric wrote: >>> 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 >>> >>> -- >>> 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 [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group at >>> http://groups.google.com/group/thinking-sphinx?hl=en. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Thinking Sphinx" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/thinking-sphinx?hl=en. >> > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
