Everything working fine then indexing takes forever after adding one line

[Thibault Clavel]

Hi everyone,

I've set up TS 3.1.0 on a rails 4 app, which is what I would call a "playing card manager".

Here is my Card index :

ThinkingSphinx::Index.define :card, :with => :active_record do

  # fields

  indexes name, :sortable => true

  indexes name_en, :sortable => true

  has colors(:id), :as => :color_ids

  has card_formats(:id), :as => :card_format_ids

  has rarities(:id), :as => :rarity_ids

  set_property :delta => true

end

Colors, Card Formats and Raritiers are all has_many through relationships with the Card model. When I run rake ts:rebuild or ts;index, it takes less than a minute to proceed.

If I add this line :

  has card_types(:id), :as => :card_type_ids

... which is another has_many :through, indexing starts ("indexing card_core") but seems to get stuck at this point. Last stime I let it run for almost 20h with no progress. :(

If I run SHOW INDEX on the join table card_card_types, I get this :

mysql> SHOW INDEX FROM card_card_types;

+-----------------+------------+---------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table           | Non_unique | Key_name                              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------------+------------+---------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| card_card_types |          0 | PRIMARY                               |            1 | id           | A         |       25550 |     NULL | NULL   |      | BTREE      |         |               |

| card_card_types |          1 | index_card_card_types_on_card_id      |            1 | card_id      | A         |       25550 |     NULL | NULL   | YES  | BTREE      |         |               |

| card_card_types |          1 | index_card_card_types_on_card_type_id |            1 | card_type_id | A         |          34 |     NULL | NULL   | YES  | BTREE      |         |               |

+-----------------+------------+---------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

which seems OK to me, since card_id and card_type_id are indexes.

Any thoughts about this ?

Thanks a lot !

[Pat Allan]

Hi Thibault

Can you add the following option to each of the attributes you’ve defined: :source => :query - and see if that helps?

Also: in TS v3, delta options are specified alongside :with => :active_record:

  ThinkingSphinx::Index.define :card, :with => :active_record, :delta => true do

    # …

  end

— 

Pat

--
You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
To unsubscribe from this group and stop receiving emails from it, send an email to thinking-sphi...@googlegroups.com.
To post to this group, send email to thinkin...@googlegroups.com.
Visit this group at http://groups.google.com/group/thinking-sphinx.
For more options, visit https://groups.google.com/groups/opt_out.

[Thibault Clavel]

Hi Pat,

 Adding :source => :query worked like a charm, I tested with other attributes thaht made indexing last forever and indexing is now lightning fast !

 What did that parameter change ?

 Thanks a lot anyways for your quick and effective answer, I was stuck on this since last week. :)

[Pat Allan]

:source => :query shifts the attribute into its own SQL statement, which is simpler and thus faster (I’m always surprised at how much faster it can be), rather than adding extra joins and complexity to the main query covers the rest of the index data.

Great to know it’s helped!

— 

Pat

[Pat Allan]

There’s no direct option for > or < - the easiest way to take that approach is to use ranges, with the non-specific values really big or really small. So, time greater than now could be :time => Time.zone.now..1.year.from_now (or whatever’s appropriate for your dataset).

On 5 Feb 2014, at 2:52 am, Thibault Clavel <thibaul...@gmail.com> wrote:

Well, indexing time went from 20+ hours to less than a second, that's pretty amazing !

While I am at it, is there a way to use the with: parameters with < and > operators ? I haven't seen this in the documentation...

[Thibault Clavel]

Thank you Pat, I found that solution in an old message of this board, that's why I had deleted my last post.

The :source => :query method works well on localhost but I tested it yesterday evening on my small dedicated server and it took the whole night without finishing, not even telling me "indexing card_core".

Is there a way to launch rake ts:rebuild RAILS_ENV=production in verbose mode to see what is happening ?

Thanks a lot.

[Thibault Clavel]

Oh it seems that there is no production.sphinx.conf in my config folder, maybe that's why it does not seem to start at all. How can I generate this one ?

[Pat Allan]

Hi Thibault

I’m guessing you’re using Capistrano? Best to make sure you’ve got configuration_file set in your thinking_sphinx.yml file to ensure that generated conf file lives in the shared directory. See the first example here:

http://pat.github.io/thinking-sphinx/deployment.html

(That setting was only added to the docs a few weeks ago, hence it may have been missed).

Once it’s there, best to SSH in, kill any searchd processes, then run ts:rebuild (via cap or directly) and see how that goes.

— 

Pat

[Thibault Clavel]

Shame on me Pat, I had not even read this page. :/

I followed the steps and everything is working. Thanks again !

By the way, in this part of the doc, the second line is ambiguous, I had to use the follow the first line instruction while I use Thinking Sphinx as a gem. The second require isn't found when deploying (I use the TS 3.1.0 gem).

# If you're using Thinking Sphinx 3.0.0 or newer
require 'thinking_sphinx/capistrano'
# If you're using Thinking Sphinx as a gem (Rails 3 way):
require 'thinking_sphinx/deploy/capistrano'

--
You received this message because you are subscribed to a topic in the Google Groups "Thinking Sphinx" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/thinking-sphinx/77A-awBd8fQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to thinking-sphi...@googlegroups.com.

[Pat Allan]

I’ve just pushed a tweak to those comments - the second line is only for TS v2.x, so you only need the first.

Great to know things are working now :)

— 

Pat