Hey Pat, I'm using PostgresSQL, and command is the same. I tried looking at the output and it kinda looks like chinese to me. >From what I see, I can see no reference to indexes whatsoever. That can be a problem. According to Postgres docs I must do an ANALYZE <table_name> to make pg server aware of an index on that specific table.
After I ve done that and run the EXPLAIN and EXPLAIN ANALYSE I still can't see any reference to indexes. That might be a problem. Do you want me to copy paste here the whole log or is there some way I can attach that file to this thread? (it's a bit lengthy) Best regards On Feb 23, 12:31 am, Pat Allan <[email protected]> wrote: > 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 > > athttp://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.
