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.
