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.
