thinking-sphinx-index-performance-2

NUMBER_OF_INDICES = 5

(1..NUMBER_OF_INDICES).each do |ind|
  ThinkingSphinx::Index.define :incident, name: "incident_index_#{ind}", with: :active_record, delta: ThinkingSphinx::Deltas::ResqueDelta do
    where "incidents.deleted = 0 AND accounts.status = 'enabled' AND (incidents.id % #{NUMBER_OF_INDICES} = #{ind - 1})"
    set_property group_concat_max_len: 8192

    indexes name, sortable: true
    indexes description, number, resolution
    indexes [incident_type.name, incident_sub_type.name], as: :category
    indexes site.name, as: :site
    indexes department.name, as: :department
    indexes state.value, as: :state
    indexes comments(:body), as: :comments
    indexes request_variables(:value), as: :variables
    indexes [requester.name, assignee.name, requester.email, assignee.email], as: :user
    indexes tags(:name), as: :tag
    indexes custom_fields_values(:value), as: :custom
    indexes cc
    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 account.status, as: :account_status
    has priority, scheduled
    has site_id, department_id, type: :bigint
    has assignee.superviser_id, as: :assignee_reports_to

    has sla_violations.created_at, as: :sla_violations_created_at
    has sla_violations.resolved, as: :sla_violations_resolved, type: :integer
    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 tasks.confirmation, as: :task_confirmation, type: :integer
    has tags.id, as: :tag_ids
    has custom_fields_values.id, as: :custom_fields_value_ids

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

(1..NUMBER_OF_INDICES).each do |ind|
  ThinkingSphinx::Index.define :incident, name: "incident_index_#{ind + 5}", with: :active_record, delta: ThinkingSphinx::Deltas::ResqueDelta do
    where "incidents.deleted = 0 AND accounts.status = 'enabled' AND (incidents.id % #{NUMBER_OF_INDICES} = #{ind - 1})"
    set_property group_concat_max_len: 8192

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

    indexes "SELECT incidents.id * 69 + 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 ORDER BY id", as: :category, source: :query
    indexes "SELECT incidents.id * 69 + 7 AS id, sites.name AS site FROM incidents LEFT OUTER JOIN sites ON sites.id = site_id ORDER BY id", as: :site, source: :query
    indexes "SELECT incidents.id * 69 + 7 AS id, departments.name AS department FROM incidents LEFT OUTER JOIN departments ON departments.id = department_id ORDER BY id", as: :department, source: :query
    indexes "SELECT incidents.id * 69 + 7 AS id, itsm_states.value AS state FROM incidents LEFT OUTER JOIN itsm_states ON itsm_states.id = state_id ORDER BY id", as: :state, source: :query
    indexes "SELECT incidents.id * 69 + 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' GROUP BY id ORDER BY id", as: :comments, source: :query
    indexes "SELECT incidents.id * 69 + 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' GROUP BY id ORDER BY id", as: :variables, source: :query
    indexes "SELECT incidents.id * 69 + 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 ORDER BY id", as: :user, source: :query
    indexes "SELECT incidents.id * 69 + 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' 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 * 69 + 7 AS id, groups.superviser_id AS assignee_reports_to FROM incidents LEFT OUTER JOIN groups ON groups.id = assignee_id ORDER BY id", as: :assignee_reports_to, source: :query, type: :integer, multi: true
    has "SELECT incidents.id * 69 + 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' GROUP BY id ORDER BY id", as: :task_confirmation, source: :query, type: :integer, multi: true
    has "SELECT incidents.id * 69 + 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' 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
end