thinking-sphinx-index-performance-solution.rb

ThinkingSphinx::Index.define(
  :incident,
  with: :active_record,
  delta?: false,
  delta_processor: ThinkingSphinx::Deltas.processor_for(ThinkingSphinx::Deltas::ResqueDelta)
) do
  where "incidents.deleted = 0 AND accounts.status = 'enabled'"
  set_property group_concat_max_len: 8192

  indexes name, sortable: true
  indexes description, number, resolution, cc

  indexes "SELECT incidents.id * 51 + 7 AS id, CONCAT_WS(' ', cat.name, sub_cat.name) AS category FROM incidents LEFT OUTER JOIN incident_types cat ON cat.id = incident_type_id  LEFT OUTER JOIN incident_types sub_cat ON sub_cat.id = incident_sub_type_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :category, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, sites.name AS site FROM incidents LEFT OUTER JOIN sites ON sites.id = site_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :site, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, departments.name AS department FROM incidents LEFT OUTER JOIN departments ON departments.id = department_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :department, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, itsm_states.value AS state FROM incidents LEFT OUTER JOIN itsm_states ON itsm_states.id = state_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :state, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT comments.body SEPARATOR ' ') AS comments FROM incidents LEFT OUTER JOIN comments ON comments.commenter_id = incidents.id AND comments.commenter_type = 'Incident' WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :comments, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT request_variables.value SEPARATOR ' ') AS variables FROM incidents LEFT OUTER JOIN request_variables ON request_variables.requestable_id = incidents.id AND request_variables.requestable_type = 'Incident' WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :variables, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, CONCAT_WS(' ', requesters.name, groups.name, requesters.email, groups.email) AS user FROM incidents LEFT OUTER JOIN groups ON groups.id = assignee_id LEFT OUTER JOIN requesters ON requesters.id = requester_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :user, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT custom_fields_values.value SEPARATOR ' ') AS custom FROM incidents LEFT OUTER JOIN custom_fields_values ON custom_fields_values.customfieldable_id = incidents.id AND custom_fields_values.customfieldable_type = 'Incident' WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :custom, source: :query

  indexes tags(:name), as: :tag
  indexes sla_violations.sla_type, as: :sla_violations_type

  has account_id, requester_id, assignee_id, created_by_id
  has created_at, updated_at, due_at
  has state_id, request_source_id, incident_type_id, incident_sub_type_id
  has priority, scheduled
  has site_id, department_id, type: :bigint

  has account.status, as: :account_status
  has sla_violations.created_at, as: :sla_violations_created_at
  has sla_violations.resolved, as: :sla_violations_resolved, type: :integer
  has tags.id, as: :tag_ids
  has statistics.statistics_type_id, as: :statistics_type_id
  has statistics.updated_at, as: :statistics_value # TODO: Add column date_value as type DATETIME
  has customer_satisfaction_surveys.grade, as: :customer_satisfaction_grade, type: :integer

  has "SELECT incidents.id * 51 + 7 AS id, groups.superviser_id AS assignee_reports_to FROM incidents LEFT OUTER JOIN groups ON groups.id = assignee_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :assignee_reports_to, source: :query, type: :integer, multi: true
  has "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT tasks.confirmation SEPARATOR ',') AS task_confirmation FROM incidents LEFT OUTER JOIN tasks ON tasks.taskable_id = incidents.id AND tasks.taskable_type = 'Incident' WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :task_confirmation, source: :query, type: :integer, multi: true
  has "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT custom_fields_values.id SEPARATOR ',') AS custom_fields_value_ids FROM incidents LEFT OUTER JOIN custom_fields_values ON custom_fields_values.customfieldable_id = incidents.id AND custom_fields_values.customfieldable_type = 'Incident' WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :custom_fields_value_ids, source: :query, type: :integer, multi: true

  has id, as: :id_number
  has "0", as: :status, type: :integer
end

ThinkingSphinx::Index.define(
  :incident,
  with: :active_record,
  delta?: true,
  delta_processor: ThinkingSphinx::Deltas.processor_for(ThinkingSphinx::Deltas::ResqueDelta)
) do
  where "incidents.deleted = 0 AND accounts.status = 'enabled' AND incidents.delta = 1"
  set_property group_concat_max_len: 8192

  indexes name, sortable: true
  indexes description, number, resolution, cc

  indexes "SELECT incidents.id * 51 + 7 AS id, CONCAT_WS(' ', cat.name, sub_cat.name) AS category FROM incidents LEFT OUTER JOIN incident_types cat ON cat.id = incident_type_id  LEFT OUTER JOIN incident_types sub_cat ON sub_cat.id = incident_sub_type_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :category, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, sites.name AS site FROM incidents LEFT OUTER JOIN sites ON sites.id = site_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :site, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, departments.name AS department FROM incidents LEFT OUTER JOIN departments ON departments.id = department_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :department, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, itsm_states.value AS state FROM incidents LEFT OUTER JOIN itsm_states ON itsm_states.id = state_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :state, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT comments.body SEPARATOR ' ') AS comments FROM incidents LEFT OUTER JOIN comments ON comments.commenter_id = incidents.id AND comments.commenter_type = 'Incident' WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :comments, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT request_variables.value SEPARATOR ' ') AS variables FROM incidents LEFT OUTER JOIN request_variables ON request_variables.requestable_id = incidents.id AND request_variables.requestable_type = 'Incident' WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :variables, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, CONCAT_WS(' ', requesters.name, groups.name, requesters.email, groups.email) AS user FROM incidents LEFT OUTER JOIN groups ON groups.id = assignee_id LEFT OUTER JOIN requesters ON requesters.id = requester_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :user, source: :query
  indexes "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT custom_fields_values.value SEPARATOR ' ') AS custom FROM incidents LEFT OUTER JOIN custom_fields_values ON custom_fields_values.customfieldable_id = incidents.id AND custom_fields_values.customfieldable_type = 'Incident' WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :custom, source: :query

  indexes tags(:name), as: :tag
  indexes sla_violations.sla_type, as: :sla_violations_type

  has account_id, requester_id, assignee_id, created_by_id
  has created_at, updated_at, due_at
  has state_id, request_source_id, incident_type_id, incident_sub_type_id
  has priority, scheduled
  has site_id, department_id, type: :bigint

  has account.status, as: :account_status
  has sla_violations.created_at, as: :sla_violations_created_at
  has sla_violations.resolved, as: :sla_violations_resolved, type: :integer
  has tags.id, as: :tag_ids
  has statistics.statistics_type_id, as: :statistics_type_id
  has statistics.updated_at, as: :statistics_value # TODO: Add column date_value as type DATETIME
  has customer_satisfaction_surveys.grade, as: :customer_satisfaction_grade, type: :integer

  has "SELECT incidents.id * 51 + 7 AS id, groups.superviser_id AS assignee_reports_to FROM incidents LEFT OUTER JOIN groups ON groups.id = assignee_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :assignee_reports_to, source: :query, type: :integer, multi: true
  has "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT tasks.confirmation SEPARATOR ',') AS task_confirmation FROM incidents LEFT OUTER JOIN tasks ON tasks.taskable_id = incidents.id AND tasks.taskable_type = 'Incident' WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :task_confirmation, source: :query, type: :integer, multi: true
  has "SELECT incidents.id * 51 + 7 AS id, GROUP_CONCAT(DISTINCT custom_fields_values.id SEPARATOR ',') AS custom_fields_value_ids FROM incidents LEFT OUTER JOIN custom_fields_values ON custom_fields_values.customfieldable_id = incidents.id AND custom_fields_values.customfieldable_type = 'Incident' WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) GROUP BY id ORDER BY id", as: :custom_fields_value_ids, source: :query, type: :integer, multi: true

  has id, as: :id_number
  has "0", as: :status, type: :integer
 end

thinking-sphinx-index-performance-stackoverflow-details.txt

https://stackoverflow.com/questions/30913789/thinking-sphinx-indexing-performance

```

This is the solution that worked best (from the linked question). Basically, you can remove a piece of the main query sql_query and define it separately as a sql_joined_field in the sphinx.conf file.

It's important to add all relevant sql conditions to each sql_joined_field (such as sharding indexes by modulo on the ID). Here's the new definition:

ThinkingSphinx::Index.define(
  :incident,
  with: :active_record,
  delta?: false,
  delta_processor: ThinkingSphinx::Deltas.processor_for(ThinkingSphinx::Deltas::ResqueDelta)
) do
    indexes "SELECT incidents.id * 51 + 7 AS id, sites.name AS site FROM incidents LEFT OUTER JOIN sites ON sites.id = site_id WHERE incidents.deleted = 0 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :site, source: :query
    ...
    has
    ...
end

ThinkingSphinx::Index.define(
  :incident,
  with: :active_record,
  delta?: true,
  delta_processor: ThinkingSphinx::Deltas.processor_for(ThinkingSphinx::Deltas::ResqueDelta)
) do
    indexes "SELECT incidents.id * 51 + 7 AS id, sites.name AS site FROM incidents LEFT OUTER JOIN sites ON sites.id = site_id WHERE incidents.deleted = 0 AND incidents.delta = 1 AND EXISTS (SELECT id FROM accounts WHERE accounts.status = 'enabled' AND incidents.account_id = id) ORDER BY id", as: :site, source: :query
    ...
    has
    ...
end
The magic that defines the field site as a separate query is the option source: :query at the end of the line.

Notice the core index definition has the parameter delta?: false, while the delta index definition has the parameter delta?: true. That's so I could use the condition WHERE incidents.delta = 1 in the delta index and filter out irrelevant records.

I found sharding didn't perform any better, so I reverted to one unified index.

See the whole index definition here: https://gist.github.com/jonsgold/05e2aea640320ee9d8b2.

Important to remember!

The Sphinx document ID offset must be handled manually. That is, whenever an index for another model is added or removed, my calculated document ID will change. This must be updated.

So, in my example, if I added an index for a different model (not :incident), I would have to run rake ts:configure to find out my new offset and change incidents.id * 51 + 7 accordingly

```

thinking_sphinx_ranged_query_examples.rb

  indexes "SELECT incidents.id * 51 + 7 AS id ,
    sites.name AS site FROM incidents 
    WHERE (incidents.id BETWEEN $start AND $end);   
    SELECT IFNULL(MIN(incidents.id), 1),
      IFNULL(MAX(incidents.id), 1) FROM incidents
  " , as: :site, source: :ranged_query


  has "SELECT incidents.id * 51 + 7 AS id , 
    GROUP_CONCAT(DISTINCT IF(site.name is not null, site.id, NULL) SEPARATOR ',') AS   site_ids
    FROM incidents 
    WHERE (incidents.id BETWEEN $start AND $end);
    SELECT IFNULL(MIN(incidents.id), 1),   
       IFNULL(MAX(incidents.id), 1) FROM incidents
  " , as: :site_ids,  facet: true, type: :integer, multi: true, source: :ranged_query