gistfile1.rb

ThinkingSphinx::Index.define :document, with: :active_record, delta: true, sql_range_step: 999999999, group_concat_max_len: 16384 do

  #~ How can I optmise this index so that I can have facets and indexes
  #~ on each association without it taking forever to run?
  #~ Having more than 3 indexes at any one time, means rake ts:index doesn't finish running.

  has "SELECT document_id * 2 + 0 as `id`, country_id as `country_ids` FROM `countries_documents` ORDER BY document_id", as: :country_ids, source: :query, facet: true, type: :integer, multi: true
  has "SELECT document_id * 2 + 0 as `id`, image_id as `image_ids` FROM `documents_images` ORDER BY document_id", as: :image_ids, source: :query, facet: true, type: :integer, multi: true
  has "SELECT document_id * 2 + 0 as `id`, subscriber_id as `subscriber_ids` FROM `documents_subscribers` ORDER BY document_id", as: :subscriber_ids, source: :query, facet: true, type: :integer, multi: true
  has "SELECT document_id * 2 + 0 as `id`, video_id as `video_ids` FROM `documents_videos` ORDER BY document_id", as: :video_ids, source: :query, facet: true, type: :integer, multi: true
  has "SELECT document_id * 2 + 0 as `id`, tag_id as `tag_ids` FROM `documents_tags` ORDER BY document_id", as: :tag_ids, source: :query, facet: true, type: :integer, multi: true

  indexes "SELECT document_id * 2 + 0 as `id`, countries.`name` as `countries` FROM `countries_documents` INNER JOIN `countries` ON `country_id` = `countries`.`id` ORDER BY document_id", as: :countries, source: :query
  indexes "SELECT document_id * 2 + 0 as `id`, images.title as `images` FROM `documents_images` INNER JOIN `images` ON `image_id` = `images`.`id` ORDER BY document_id", as: :images, source: :query
  indexes "SELECT document_id * 2 + 0 as `id`, subscribers.title as `subscribers` FROM `documents_subscribers` INNER JOIN `subscribers` ON `subscriber_id` = `subscribers`.`id` ORDER BY document_id", as: :subscribers, source: :query
  indexes "SELECT document_id * 2 + 0 as `id`, videos.title as `videos` FROM `documents_videos` INNER JOIN `videos` ON `video_id` = `videos`.`id` ORDER BY document_id", as: :videos, source: :query
  indexes "SELECT document_id * 2 + 0 as `id`, tags.`name` as `tags` FROM `documents_tags` INNER JOIN `tags` ON `tag_id` = `tags`.`id` ORDER BY document_id", as: :tags, source: :query

  has updated_at

end

Hi pat,

I am using sphinx, It's very nice. I implemented above code in one of my project, for 500 records it's working good and showing facet count but for 10000 records there is no count showing. With this code indexing is very fast. Facets count have some erratic behavior with some filters, it not displaying count even it have results.

thanks in advance.