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.
