Re: [ts] Facets causing huge query at runtime

[Pat Allan]

Hi Martin

I would recommend shifting location into its own model/table - that way, you can then filter by location_id as a multi-value integer facet, which removes the need to CRC32 each location value and load User models (and with that many values, CRC32 could easily have collisions for different strings).

Cheers

--
Pat

On 13/12/2012, at 1:59 AM, Martin Streicher wrote:

> I have a Rails model named User. The model has a field named location with contents such as 'Miami, FL'.
>
> I set this field to be a facet.
>
> There are about 135K user records.
>
> When I run something like
>
> User.facets facets: [:location]
>
> it causes a MySQL query of SELECT * from users. After a while, it does yield a hash of facets with locations and counts, but it takes too long to be viable in production.
>
> I want to use the facets -- location and a few others -- to provide a context sensitive search refinement. I need the string values of location.
>
> Is there any good way to do this? Why do the facets cause the query? I assume the CRC32 values are stored in Sphinx, but it needs the strings to do the human-readable mapping?
>
>
>
> --
> 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/-/PHEFtkynXnsJ.
> 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.

[Martin Streicher]

Thanks, Pat. 

So, let's say I have a huge catalog of products. If I wanted to facet on author name, I would create a table for the authors and use those IDs as facets?

Or here, move the city names to a table and then link to the proper city record from the other table. The city_id acts as the facets. 

Will Sphinx have to look up the city names by loading the city table? Or how does the massive SELECT get avoided?

[Martin Streicher]

Pat -- see my follow-up question. 

[Pat Allan]

Hi Martin

On 14/12/2012, at 4:18 AM, Martin Streicher wrote:

> So, let's say I have a huge catalog of products. If I wanted to facet on author name, I would create a table for the authors and use those IDs as facets?

Yes, that's a better approach than using the author name directly for the facets.

> Or here, move the city names to a table and then link to the proper city record from the other table. The city_id acts as the facets.
>
> Will Sphinx have to look up the city names by loading the city table? Or how does the massive SELECT get avoided?

No, Sphinx and Thinking Sphinx will just use the city id values for facet calculations if that's what is being used for facets. There will be no need for large SELECTs on the cities table.

Cheers

--
Pat

>
>
>
>
> On Wednesday, 12 December 2012 18:07:07 UTC-5, Pat Allan wrote:
> Hi Martin
> I would recommend shifting location into its own model/table - that way, you can then filter by location_id as a multi-value integer facet, which removes the need to CRC32 each location value and load User models (and with that many values, CRC32 could easily have collisions for different strings).
>
> Cheers
>
> --
> Pat
>
>
> On 13/12/2012, at 1:59 AM, Martin Streicher wrote:
>
> > I have a Rails model named User. The model has a field named location with contents such as 'Miami, FL'.
> >
> > I set this field to be a facet.
> >
> > There are about 135K user records.
> >
> > When I run something like
> >
> > User.facets facets: [:location]
> >
> > it causes a MySQL query of SELECT * from users. After a while, it does yield a hash of facets with locations and counts, but it takes too long to be viable in production.
> >
> > I want to use the facets -- location and a few others -- to provide a context sensitive search refinement. I need the string values of location.
> >
> > Is there any good way to do this? Why do the facets cause the query? I assume the CRC32 values are stored in Sphinx, but it needs the strings to do the human-readable mapping?
> >
> >
> >
> > --
> > 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/-/PHEFtkynXnsJ.
> > 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.
>
>
>
>
>
>
>
>
>
>
> --
> 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/-/RAcXNVxSZl0J.