Hi Pedro
Given you've got the database indexes, this definitely feels far too slow. Are
you using MySQL or PostgreSQL?
If you're using MySQL, then I would recommend taking the generated sql_query
value from the restaurant_core_0 source in your sphinx configuration file,
remove the $start and $end parts of the WHERE clause, and add EXPLAIN to the
start of the query. Then, run this query in a MySQL console:
EXPLAIN SELECT ...
This should give you some information on what database indexes are being used
for the query.
I'm not sure what the equivalent of the EXPLAIN command for PostgreSQL is, but
I'd be surprised if such a thing didn't exist.
Cheers
--
Pat
On 23/02/2011, at 1:32 AM, Pedro Cunha wrote:
> Hello there,
>
> I'm having a weird slow indexing. Tried looking for similar issues on
> this group and couldn't find.
>
> When I index an has_many through association, perfomance decays a lot.
> To help trace this issue I ll give as much info as I can.
>
> So the association looks like this:
>
> Restaurant (1 : N ) Menu Categories
> Menu Categories ( 1 : N ) Menu Items
>
> Index on table menu_categories for restaurant_id (DB level)
> Index on table menu_items for menu_category_id (DB level)
>
> define_index on restaurant
> ...
> indexes menu_categories.menu_items(:name), :as => :menu_item_name
> indexes menu_categories.menu_items(:subtitle), :as
> => :menu_item_subtitle
> indexes menu_categories.menu_items(:description), :as
> => :menu_item_description
> ...
> end
>
> Without the indexes before, and with a lot of other stuff I get this
> perfomance:
>
> indexing index 'restaurant_core'...
> collected 12 docs, 3.0 MB
> collected 122936 attr values
> sorted 0.2 Mvalues, 100.0% done
> sorted 2.2 Mhits, 100.0% done
> total 12 docs, 2973437 bytes
> total 16.741 sec, 177611 bytes/sec, 0.71 docs/sec
> distributed index 'restaurant' can not be directly indexed; skipping.
>
> With the indexes
>
> indexing index 'restaurant_core'...
> collected 12 docs, 15.9 MB
> collected 423920 attr values
> sorted 0.8 Mvalues, 100.0% done
> sorted 11.4 Mhits, 100.0% done
> total 12 docs, 15885288 bytes
> total 320.783 sec, 49520 bytes/sec, 0.03 docs/sec
> distributed index 'restaurant' can not be directly indexed; skipping.
>
>
>
> table ids are pretty small so I think that issue is not causing
> problems.. still I tried adding the sql step config and performance
> stays the same
>
> Properties on define_index are:
>
> set_property :min_prefix_len => 3
> set_property :stopwords => "#{RAILS_ROOT}/config/sphinx/
> stoplist.txt"
> set_property :delta => ThinkingSphinx::Deltas::ResqueDelta
>
> Tried commenting min_prefix_len and perfomance keeps the same..
>
> Running out of ideas :(
>
>
> Best regards,
> Pedro
>
>
>
>
>
> --
> 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.
