thinking-sphinx-index-performance.txt

########################
# The index definition #
########################

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


########################################
# The generated query (five like this) #
########################################

source incident_index_1_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = root
  sql_pass =
  sql_db = samanage_development
  sql_sock = /tmp/mysql.sock
  sql_query_pre = SET TIME_ZONE = '+0:00'
  sql_query_pre = SET SESSION group_concat_max_len = 8192

  sql_query =
  SELECT SQL_NO_CACHE
    `incidents`.`id` * 56 + 6 AS `id`,
    'Incident' AS `sphinx_internal_class_name`,
    incidents.`name` AS `name`,
    incidents.`description` AS `description`,
    incidents.`number` AS `number`,
    incidents.`resolution` AS `resolution`,
    CONCAT_WS(' ', incident_types.`name`, incident_sub_types_incidents.`name`) AS `category`,
    sites.`name` AS `site`,
    departments.`name` AS `department`,
    itsm_states.`value` AS `state`,
    GROUP_CONCAT(DISTINCT comments.`body` SEPARATOR ' ') AS `comments`,
    GROUP_CONCAT(DISTINCT request_variables.`value` SEPARATOR ' ') AS `variables`,
    CONCAT_WS(' ', requesters.`name`, groups.`name`, requesters.`email`, groups.`email`) AS `user`,
    GROUP_CONCAT(DISTINCT tags.`name` SEPARATOR ' ') AS `tag`,
    GROUP_CONCAT(DISTINCT custom_fields_values.`value` SEPARATOR ' ') AS `custom`,
    incidents.`cc` AS `cc`,
    GROUP_CONCAT(DISTINCT sla_violations.`sla_type` SEPARATOR ' ') AS `sla_violations_type`,
    incidents.`id` AS `sphinx_internal_id`,
    'Incident' AS `sphinx_internal_class`,
    0 AS `sphinx_deleted`,
    incidents.`account_id` AS `account_id`,
    incidents.`requester_id` AS `requester_id`,
    incidents.`assignee_id` AS `assignee_id`,
    incidents.`created_by_id` AS `created_by_id`,
    UNIX_TIMESTAMP(incidents.`created_at`) AS `created_at`,
    UNIX_TIMESTAMP(incidents.`updated_at`) AS `updated_at`,
    UNIX_TIMESTAMP(incidents.`due_at`) AS `due_at`,
    incidents.`state_id` AS `state_id`,
    incidents.`request_source_id` AS `request_source_id`,
    incidents.`incident_type_id` AS `incident_type_id`,
    incidents.`incident_sub_type_id` AS `incident_sub_type_id`,
    accounts.`status` AS `account_status`,
    incidents.`priority` AS `priority`,
    incidents.`scheduled` AS `scheduled`,
    incidents.`site_id` AS `site_id`,
    incidents.`department_id` AS `department_id`,
    groups.`superviser_id` AS `assignee_reports_to`,
    GROUP_CONCAT(DISTINCT UNIX_TIMESTAMP(sla_violations.`created_at`) SEPARATOR ',') AS `sla_violations_created_at`,
    GROUP_CONCAT(DISTINCT sla_violations.`resolved` SEPARATOR ',') AS `sla_violations_resolved`,
    GROUP_CONCAT(DISTINCT statistics.`statistics_type_id` SEPARATOR ',') AS `statistics_type_id`,
    GROUP_CONCAT(DISTINCT UNIX_TIMESTAMP(statistics.`updated_at`) SEPARATOR ',') AS `statistics_value`,
    GROUP_CONCAT(DISTINCT customer_satisfaction_surveys.`grade` SEPARATOR ',') AS `customer_satisfaction_grade`,
    GROUP_CONCAT(DISTINCT tasks.`confirmation` SEPARATOR ',') AS `task_confirmation`,
    GROUP_CONCAT(DISTINCT tags.`id` SEPARATOR ',') AS `tag_ids`,
    GROUP_CONCAT(DISTINCT custom_fields_values.`id` SEPARATOR ',') AS `custom_fields_value_ids`,
    incidents.`id` AS `id_number`,
    0 AS `status`
  FROM `incidents`
    LEFT OUTER JOIN `incident_types` ON `incident_types`.`id` = `incidents`.`incident_type_id`
    LEFT OUTER JOIN `incident_types` `incident_sub_types_incidents` ON `incident_sub_types_incidents`.`id` = `incidents`.`incident_sub_type_id`
    LEFT OUTER JOIN `sites` ON `sites`.`id` = `incidents`.`site_id`
    LEFT OUTER JOIN `departments` ON `departments`.`id` = `incidents`.`department_id`
    LEFT OUTER JOIN `itsm_states` ON `itsm_states`.`id` = `incidents`.`state_id`
    LEFT OUTER JOIN `comments` ON `comments`.`commenter_id` = `incidents`.`id` AND `comments`.`commenter_type` = 'Incident'
    LEFT OUTER JOIN `request_variables` ON `request_variables`.`requestable_id` = `incidents`.`id` AND `request_variables`.`requestable_type` = 'Incident'
    LEFT OUTER JOIN `requesters` ON `requesters`.`id` = `incidents`.`requester_id`
    LEFT OUTER JOIN `groups` ON `groups`.`id` = `incidents`.`assignee_id`
    LEFT OUTER JOIN `taggings` ON `taggings`.`taggable_id` = `incidents`.`id` AND `taggings`.`taggable_type` = 'Incident' AND `taggings`.`context` = 'tags'
    LEFT OUTER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id`
    LEFT OUTER JOIN `custom_fields_values` ON `custom_fields_values`.`customfieldable_id` = `incidents`.`id` AND `custom_fields_values`.`customfieldable_type` = 'Incident'
    LEFT OUTER JOIN `sla_violations` ON `sla_violations`.`slable_id` = `incidents`.`id` AND `sla_violations`.`slable_type` = 'Incident'
    LEFT OUTER JOIN `accounts` ON `accounts`.`id` = `incidents`.`account_id`
    LEFT OUTER JOIN `statistics` ON `statistics`.`statisticable_id` = `incidents`.`id` AND `statistics`.`statisticable_type` = 'Incident'
    LEFT OUTER JOIN `customer_satisfaction_surveys` ON `customer_satisfaction_surveys`.`incident_id` = `incidents`.`id`
    LEFT OUTER JOIN `tasks` ON `tasks`.`taskable_id` = `incidents`.`id` AND `tasks`.`taskable_type` = 'Incident'
  WHERE
    (`incidents`.`id` BETWEEN $start AND $end AND incidents.deleted = 0 AND accounts.status = 'enabled' AND (incidents.id % 5 = 0))
  GROUP BY
    `incidents`.`id`, incidents.`name`, incidents.`description`, incidents.`number`, 
    incidents.`resolution`, incident_types.`name`, incident_sub_types_incidents.`name`, 
    sites.`name`, departments.`name`, itsm_states.`value`, requesters.`name`, groups.`name`, 
    requesters.`email`, groups.`email`, incidents.`cc`, incidents.`id`, incidents.`account_id`, 
    incidents.`requester_id`, incidents.`assignee_id`, incidents.`created_by_id`, incidents.`created_at`, 
    incidents.`updated_at`, incidents.`due_at`, incidents.`state_id`, incidents.`request_source_id`, 
    incidents.`incident_type_id`, incidents.`incident_sub_type_id`, accounts.`status`, incidents.`priority`, 
    incidents.`scheduled`, incidents.`site_id`, incidents.`department_id`, groups.`superviser_id`, incidents.`id`
  ORDER BY NULL

  sql_query_range = SELECT IFNULL(MIN(`incidents`.`id`), 1), IFNULL(MAX(`incidents`.`id`), 1) FROM `incidents`
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = account_id
  sql_attr_uint = requester_id
  sql_attr_uint = assignee_id
  sql_attr_uint = created_by_id
  sql_attr_uint = state_id
  sql_attr_uint = request_source_id
  sql_attr_uint = incident_type_id
  sql_attr_uint = incident_sub_type_id
  sql_attr_uint = priority
  sql_attr_uint = assignee_reports_to
  sql_attr_uint = id_number
  sql_attr_uint = status
  sql_attr_bool = scheduled
  sql_attr_bigint = site_id
  sql_attr_bigint = department_id
  sql_attr_timestamp = created_at
  sql_attr_timestamp = updated_at
  sql_attr_timestamp = due_at
  sql_attr_multi = timestamp sla_violations_created_at from field
  sql_attr_multi = uint sla_violations_resolved from field
  sql_attr_multi = uint statistics_type_id from field
  sql_attr_multi = timestamp statistics_value from field
  sql_attr_multi = uint customer_satisfaction_grade from field
  sql_attr_multi = uint task_confirmation from field
  sql_attr_multi = uint tag_ids from field
  sql_attr_multi = uint custom_fields_value_ids from field
  sql_attr_string = sphinx_internal_class
  sql_attr_string = account_status
  sql_field_string = name
  sql_query_info = SELECT `incidents`.* FROM `incidents`  WHERE (`incidents`.`id` = ($id - 6) / 56)
}