thinking-sphinx-index-performance-solution

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