Skip to content

Instantly share code, notes, and snippets.

@danil

danil/gist:616532

Created October 8, 2010 08:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danil/616532 to your computer and use it in GitHub Desktop.
Save danil/616532 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.mkd

Sphinx version

Sphinx 0.9.9 version instaled:

* app-misc/sphinx
     Available versions:  0.9.8.1!m 0.9.9!t ~0.9.9-r1 ~1.10_beta {debug id64 mysql odbc postgres stemmer test}
     Installed versions:  Version: 0.9.9!t
                          Date:    04:40:03 PM 09/21/2010
                          USE:     mysql postgres -debug -id64 -stemmer -test
     Best versions/slot:  0.9.9!t
     Homepage:            http://www.sphinxsearch.com/
     Description:         Full-text search engine with support for MySQL and PostgreSQL
     License:             GPL-2

Thinking Sphinx index

define_index do
  indexes "LOWER(projects.title)", :as => :title, :prefix => true, :sortable => true
  indexes description
  indexes contractor_requirements
  indexes employer(:name), :as => :employer_name
  indexes skills(:title), :as => :skills
  indexes branch(:title), :as => :branch
  indexes realm(:title),  :as => :realm
  indexes state

  #has projects(:id), :as => :project_id
  has employer_id
  has contractor_city_id
  has safe_deal, :type => :integer
  has fixed_price, :type => :integer
  has rate, :type => :integer
  has min_price, :type => :integer
  has bids_count, :type => :integer
  has created_at
  has realm_id,    :as => :realm_id
  has branch_id,   :as => :branch_ids
  has skills(:id), :as => :skill_ids

  where "(projects.state = 'published' OR projects.state = 'started')"

  set_property :delta => :datetime, :threshold => 10.minutes
  set_property :min_prefix_len => Project::MIN_PREFIX_LEN
  set_property :enable_star => true
  set_property :field_weights => {
    :title => 10,
    :employer_name => 10,
    :skills => 5,
    :branch => 5,
    :realm => 5,
    :description => 1
  }
  # <http://sphinxsearch.com/docs/manual-0.9.9.html#conf-sql-query-killlist>.
  set_property :sql_query_killlist => "SELECT id FROM #{Project.quoted_table_name} WHERE state='archived' OR state='pfrozen' OR state='started' OR state='unpublished'"
end

def self.search_for_user(keywords, user, options = {})
  user_options = {}
  user_options = {:employer_id => user.id} if user.role?(:employer)
  return [] if user.role?(:contractor) && user.participated_project_ids.blank?
  user_options = {:project_id => user.participated_project_ids} if user.role?(:contractor)
  options.merge!(:with => user_options)
  Project.search(keywords, options)
end

development.sphinx.conf

indexer
{
}

searchd
{
  listen = 127.0.0.1:9312
  log = /home/danil/src/vendor/prohq/avers/log/searchd.log
  query_log = /home/danil/src/vendor/prohq/avers/log/searchd.query.log
  pid_file = /home/danil/src/vendor/prohq/avers/log/searchd.development.pid
}

source announcement_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `announcements`.`id` * 5 + 0 AS `id` , CAST(`announcements`.`title` AS CHAR) AS `title`, CAST(`announcements`.`content` AS CHAR) AS `content`, `announcements`.`id` AS `sphinx_internal_id`, 1434976996 AS `class_crc`, '1434976996' AS `subclass_crcs`, 0 AS `sphinx_deleted`, `announcements`.`category` AS `category`, UNIX_TIMESTAMP(`announcements`.`created_at`) AS `created_at` FROM `announcements`    WHERE `announcements`.`id` >= $start AND `announcements`.`id` <= $end GROUP BY `announcements`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `announcements` 
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = category
  sql_attr_timestamp = created_at
  sql_attr_multi = uint subclass_crcs from field
  sql_query_info = SELECT * FROM `announcements` WHERE `id` = (($id - 0) / 5)
}

index announcement_core
{
  source = announcement_core_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/announcement_core
  charset_type = utf-8
}

source announcement_delta_0 : announcement_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = 
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `announcements`.`id` * 5 + 0 AS `id` , CAST(`announcements`.`title` AS CHAR) AS `title`, CAST(`announcements`.`content` AS CHAR) AS `content`, `announcements`.`id` AS `sphinx_internal_id`, 1434976996 AS `class_crc`, '1434976996' AS `subclass_crcs`, 0 AS `sphinx_deleted`, `announcements`.`category` AS `category`, UNIX_TIMESTAMP(`announcements`.`created_at`) AS `created_at` FROM `announcements`    WHERE `announcements`.`id` >= $start AND `announcements`.`id` <= $end AND `announcements`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND) GROUP BY `announcements`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `announcements` WHERE `announcements`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND)
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = category
  sql_attr_timestamp = created_at
  sql_attr_multi = uint subclass_crcs from field
  sql_query_info = SELECT * FROM `announcements` WHERE `id` = (($id - 0) / 5)
}

index announcement_delta : announcement_core
{
  source = announcement_delta_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/announcement_delta
}

index announcement
{
  type = distributed
  local = announcement_delta
  local = announcement_core
}

source article_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `articles`.`id` * 5 + 1 AS `id` , CAST(`articles`.`title` AS CHAR) AS `title`, CAST(`articles`.`content` AS CHAR) AS `content`, `articles`.`id` AS `sphinx_internal_id`, 3448190970 AS `class_crc`, '3448190970' AS `subclass_crcs`, 0 AS `sphinx_deleted` FROM `articles`    WHERE `articles`.`id` >= $start AND `articles`.`id` <= $end GROUP BY `articles`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `articles` 
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_multi = uint subclass_crcs from field
  sql_query_info = SELECT * FROM `articles` WHERE `id` = (($id - 1) / 5)
}

index article_core
{
  source = article_core_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/article_core
  charset_type = utf-8
}

source article_delta_0 : article_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = 
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `articles`.`id` * 5 + 1 AS `id` , CAST(`articles`.`title` AS CHAR) AS `title`, CAST(`articles`.`content` AS CHAR) AS `content`, `articles`.`id` AS `sphinx_internal_id`, 3448190970 AS `class_crc`, '3448190970' AS `subclass_crcs`, 0 AS `sphinx_deleted` FROM `articles`    WHERE `articles`.`id` >= $start AND `articles`.`id` <= $end AND `articles`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND) GROUP BY `articles`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `articles` WHERE `articles`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND)
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_multi = uint subclass_crcs from field
  sql_query_info = SELECT * FROM `articles` WHERE `id` = (($id - 1) / 5)
}

index article_delta : article_core
{
  source = article_delta_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/article_delta
}

index article
{
  type = distributed
  local = article_delta
  local = article_core
}

source private_message_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `private_messages`.`id` * 5 + 2 AS `id` , CAST(`private_messages`.`subject` AS CHAR) AS `subject`, CAST(`private_messages`.`body` AS CHAR) AS `body`, CAST(`users`.`name` AS CHAR) AS `author`, `private_messages`.`id` AS `sphinx_internal_id`, 1618554985 AS `class_crc`, '1618554985' AS `subclass_crcs`, 0 AS `sphinx_deleted`, `private_messages`.`kind` AS `kind`, `private_messages`.`recipient_id` AS `recipient_id`, `private_messages`.`author_id` AS `author_id`, `private_messages`.`archived` AS `archived`, UNIX_TIMESTAMP(`private_messages`.`created_at`) AS `created_at` FROM `private_messages`    LEFT OUTER JOIN `users` ON `users`.id = `private_messages`.author_id  WHERE `private_messages`.`id` >= $start AND `private_messages`.`id` <= $end GROUP BY `private_messages`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `private_messages` 
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = kind
  sql_attr_uint = recipient_id
  sql_attr_uint = author_id
  sql_attr_bool = archived
  sql_attr_timestamp = created_at
  sql_attr_multi = uint subclass_crcs from field
  sql_query_info = SELECT * FROM `private_messages` WHERE `id` = (($id - 2) / 5)
}

index private_message_core
{
  source = private_message_core_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/private_message_core
  charset_type = utf-8
}

source private_message_delta_0 : private_message_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = 
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `private_messages`.`id` * 5 + 2 AS `id` , CAST(`private_messages`.`subject` AS CHAR) AS `subject`, CAST(`private_messages`.`body` AS CHAR) AS `body`, CAST(`users`.`name` AS CHAR) AS `author`, `private_messages`.`id` AS `sphinx_internal_id`, 1618554985 AS `class_crc`, '1618554985' AS `subclass_crcs`, 0 AS `sphinx_deleted`, `private_messages`.`kind` AS `kind`, `private_messages`.`recipient_id` AS `recipient_id`, `private_messages`.`author_id` AS `author_id`, `private_messages`.`archived` AS `archived`, UNIX_TIMESTAMP(`private_messages`.`created_at`) AS `created_at` FROM `private_messages`    LEFT OUTER JOIN `users` ON `users`.id = `private_messages`.author_id  WHERE `private_messages`.`id` >= $start AND `private_messages`.`id` <= $end AND `private_messages`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND) GROUP BY `private_messages`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `private_messages` WHERE `private_messages`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND)
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = kind
  sql_attr_uint = recipient_id
  sql_attr_uint = author_id
  sql_attr_bool = archived
  sql_attr_timestamp = created_at
  sql_attr_multi = uint subclass_crcs from field
  sql_query_info = SELECT * FROM `private_messages` WHERE `id` = (($id - 2) / 5)
}

index private_message_delta : private_message_core
{
  source = private_message_delta_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/private_message_delta
}

index private_message
{
  type = distributed
  local = private_message_delta
  local = private_message_core
}

source project_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `projects`.`id` * 5 + 3 AS `id` , CAST(LOWER(projects.title) AS CHAR) AS `title`, CAST(`projects`.`description` AS CHAR) AS `description`, CAST(`projects`.`contractor_requirements` AS CHAR) AS `contractor_requirements`, CAST(`users`.`name` AS CHAR) AS `employer_name`, CAST(GROUP_CONCAT(DISTINCT IFNULL(`skills`.`title`, '0') SEPARATOR ' ') AS CHAR) AS `skills`, CAST(`branches`.`title` AS CHAR) AS `branch`, CAST(`realms`.`title` AS CHAR) AS `realm`, CAST(`projects`.`state` AS CHAR) AS `state`, `projects`.`id` AS `sphinx_internal_id`, 3759073650 AS `class_crc`, '3759073650' AS `subclass_crcs`, 0 AS `sphinx_deleted`, IFNULL(LOWER(projects.title), '') AS `title_sort`, `projects`.`employer_id` AS `employer_id`, `projects`.`contractor_city_id` AS `contractor_city_id`, `projects`.`safe_deal` AS `safe_deal`, `projects`.`fixed_price` AS `fixed_price`, `projects`.`rate` AS `rate`, `projects`.`min_price` AS `min_price`, `projects`.`bids_count` AS `bids_count`, UNIX_TIMESTAMP(`projects`.`created_at`) AS `created_at`, `projects`.`realm_id` AS `realm_id`, `projects`.`branch_id` AS `branch_ids`, GROUP_CONCAT(DISTINCT IFNULL(`skills`.`id`, '0') SEPARATOR ',') AS `skill_ids` FROM `projects`    LEFT OUTER JOIN `users` ON `users`.id = `projects`.employer_id   LEFT OUTER JOIN `project_skill_associations` ON (`projects`.`id` = `project_skill_associations`.`project_id`)  LEFT OUTER JOIN `skills` ON (`skills`.`id` = `project_skill_associations`.`skill_id`)   LEFT OUTER JOIN `branches` ON `branches`.id = `projects`.branch_id   LEFT OUTER JOIN `realms` ON `realms`.id = `projects`.realm_id  WHERE `projects`.`id` >= $start AND `projects`.`id` <= $end GROUP BY `projects`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `projects` 
  sql_query_killlist = SELECT id FROM `projects` WHERE state='archived' OR state='pfrozen' OR state='started' OR state='unpublished'
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = employer_id
  sql_attr_uint = contractor_city_id
  sql_attr_uint = safe_deal
  sql_attr_uint = fixed_price
  sql_attr_uint = rate
  sql_attr_uint = min_price
  sql_attr_uint = bids_count
  sql_attr_uint = realm_id
  sql_attr_uint = branch_ids
  sql_attr_timestamp = created_at
  sql_attr_str2ordinal = title_sort
  sql_attr_multi = uint subclass_crcs from field
  sql_attr_multi = uint skill_ids from field
  sql_query_info = SELECT * FROM `projects` WHERE `id` = (($id - 3) / 5)
}

index project_core
{
  source = project_core_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/project_core
  charset_type = utf-8
  min_prefix_len = 3
  enable_star = 1
}

source project_delta_0 : project_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = 
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `projects`.`id` * 5 + 3 AS `id` , CAST(LOWER(projects.title) AS CHAR) AS `title`, CAST(`projects`.`description` AS CHAR) AS `description`, CAST(`projects`.`contractor_requirements` AS CHAR) AS `contractor_requirements`, CAST(`users`.`name` AS CHAR) AS `employer_name`, CAST(GROUP_CONCAT(DISTINCT IFNULL(`skills`.`title`, '0') SEPARATOR ' ') AS CHAR) AS `skills`, CAST(`branches`.`title` AS CHAR) AS `branch`, CAST(`realms`.`title` AS CHAR) AS `realm`, CAST(`projects`.`state` AS CHAR) AS `state`, `projects`.`id` AS `sphinx_internal_id`, 3759073650 AS `class_crc`, '3759073650' AS `subclass_crcs`, 0 AS `sphinx_deleted`, IFNULL(LOWER(projects.title), '') AS `title_sort`, `projects`.`employer_id` AS `employer_id`, `projects`.`contractor_city_id` AS `contractor_city_id`, `projects`.`safe_deal` AS `safe_deal`, `projects`.`fixed_price` AS `fixed_price`, `projects`.`rate` AS `rate`, `projects`.`min_price` AS `min_price`, `projects`.`bids_count` AS `bids_count`, UNIX_TIMESTAMP(`projects`.`created_at`) AS `created_at`, `projects`.`realm_id` AS `realm_id`, `projects`.`branch_id` AS `branch_ids`, GROUP_CONCAT(DISTINCT IFNULL(`skills`.`id`, '0') SEPARATOR ',') AS `skill_ids` FROM `projects`    LEFT OUTER JOIN `users` ON `users`.id = `projects`.employer_id   LEFT OUTER JOIN `project_skill_associations` ON (`projects`.`id` = `project_skill_associations`.`project_id`)  LEFT OUTER JOIN `skills` ON (`skills`.`id` = `project_skill_associations`.`skill_id`)   LEFT OUTER JOIN `branches` ON `branches`.id = `projects`.branch_id   LEFT OUTER JOIN `realms` ON `realms`.id = `projects`.realm_id  WHERE `projects`.`id` >= $start AND `projects`.`id` <= $end AND `projects`.`updated_at` > DATE_SUB(NOW(), INTERVAL 86400 SECOND) GROUP BY `projects`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `projects` WHERE `projects`.`updated_at` > DATE_SUB(NOW(), INTERVAL 86400 SECOND)
  sql_query_killlist = SELECT id FROM `projects` WHERE state='archived' OR state='pfrozen' OR state='started' OR state='unpublished'
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = employer_id
  sql_attr_uint = contractor_city_id
  sql_attr_uint = safe_deal
  sql_attr_uint = fixed_price
  sql_attr_uint = rate
  sql_attr_uint = min_price
  sql_attr_uint = bids_count
  sql_attr_uint = realm_id
  sql_attr_uint = branch_ids
  sql_attr_timestamp = created_at
  sql_attr_str2ordinal = title_sort
  sql_attr_multi = uint subclass_crcs from field
  sql_attr_multi = uint skill_ids from field
  sql_query_info = SELECT * FROM `projects` WHERE `id` = (($id - 3) / 5)
}

index project_delta : project_core
{
  source = project_delta_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/project_delta
}

index project
{
  type = distributed
  local = project_delta
  local = project_core
}

source user_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `users`.`id` * 5 + 4 AS `id` , CAST(LOWER(users.name) AS CHAR) AS `name`, CAST(`users`.`short_description` AS CHAR) AS `short_description`, CAST(`users`.`description` AS CHAR) AS `description`, CAST(`cities`.`name` AS CHAR) AS `city`, CAST(GROUP_CONCAT(DISTINCT IFNULL(`skills`.`title`, '0') SEPARATOR ' ') AS CHAR) AS `skills`, CAST(GROUP_CONCAT(DISTINCT IFNULL(`branches`.`title`, '0') SEPARATOR ' ') AS CHAR) AS `branches`, CAST(`realms`.`title` AS CHAR) AS `realm`, `users`.`id` AS `sphinx_internal_id`, 765557111 AS `class_crc`, '765557111' AS `subclass_crcs`, 0 AS `sphinx_deleted`, IFNULL(LOWER(users.name), '') AS `name_sort`, users.id AS `user_id`, `users`.`roles` AS `roles`, `users`.`city_id` AS `city_id`, `users`.`realm_id` AS `realm_id`, `users`.`rate` AS `rate`, `users`.`min_price` AS `min_price`, `users`.`finished_projects_count` AS `finished_projects_count`, users.total_index_value*10 AS `review_rating`, GROUP_CONCAT(DISTINCT IFNULL(`branches`.`id`, '0') SEPARATOR ',') AS `branch_ids`, GROUP_CONCAT(DISTINCT IFNULL(`skills`.`id`, '0') SEPARATOR ',') AS `skill_ids` FROM `users`    LEFT OUTER JOIN `cities` ON `cities`.id = `users`.city_id   LEFT OUTER JOIN `user_skill_associations` ON (`users`.`id` = `user_skill_associations`.`user_id`)  LEFT OUTER JOIN `skills` ON (`skills`.`id` = `user_skill_associations`.`skill_id`)   LEFT OUTER JOIN `user_branch_associations` ON (`users`.`id` = `user_branch_associations`.`user_id`)  LEFT OUTER JOIN `branches` ON (`branches`.`id` = `user_branch_associations`.`branch_id`)   LEFT OUTER JOIN `realms` ON `realms`.id = `users`.realm_id  WHERE `users`.`id` >= $start AND `users`.`id` <= $end AND state = 'active' GROUP BY `users`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `users` 
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = user_id
  sql_attr_uint = roles
  sql_attr_uint = city_id
  sql_attr_uint = realm_id
  sql_attr_uint = rate
  sql_attr_uint = min_price
  sql_attr_uint = finished_projects_count
  sql_attr_uint = review_rating
  sql_attr_str2ordinal = name_sort
  sql_attr_multi = uint subclass_crcs from field
  sql_attr_multi = uint branch_ids from field
  sql_attr_multi = uint skill_ids from field
  sql_query_info = SELECT * FROM `users` WHERE `id` = (($id - 4) / 5)
}

index user_core
{
  source = user_core_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/user_core
  charset_type = utf-8
  min_prefix_len = 2
  enable_star = 1
}

source user_delta_0 : user_core_0
{
  type = mysql
  sql_host = localhost
  sql_user = avers
  sql_pass = zOsIBxrMhJQy
  sql_db = avers
  sql_query_pre = 
  sql_query_pre = SET NAMES utf8
  sql_query = SELECT SQL_NO_CACHE `users`.`id` * 5 + 4 AS `id` , CAST(LOWER(users.name) AS CHAR) AS `name`, CAST(`users`.`short_description` AS CHAR) AS `short_description`, CAST(`users`.`description` AS CHAR) AS `description`, CAST(`cities`.`name` AS CHAR) AS `city`, CAST(GROUP_CONCAT(DISTINCT IFNULL(`skills`.`title`, '0') SEPARATOR ' ') AS CHAR) AS `skills`, CAST(GROUP_CONCAT(DISTINCT IFNULL(`branches`.`title`, '0') SEPARATOR ' ') AS CHAR) AS `branches`, CAST(`realms`.`title` AS CHAR) AS `realm`, `users`.`id` AS `sphinx_internal_id`, 765557111 AS `class_crc`, '765557111' AS `subclass_crcs`, 0 AS `sphinx_deleted`, IFNULL(LOWER(users.name), '') AS `name_sort`, users.id AS `user_id`, `users`.`roles` AS `roles`, `users`.`city_id` AS `city_id`, `users`.`realm_id` AS `realm_id`, `users`.`rate` AS `rate`, `users`.`min_price` AS `min_price`, `users`.`finished_projects_count` AS `finished_projects_count`, users.total_index_value*10 AS `review_rating`, GROUP_CONCAT(DISTINCT IFNULL(`branches`.`id`, '0') SEPARATOR ',') AS `branch_ids`, GROUP_CONCAT(DISTINCT IFNULL(`skills`.`id`, '0') SEPARATOR ',') AS `skill_ids` FROM `users`    LEFT OUTER JOIN `cities` ON `cities`.id = `users`.city_id   LEFT OUTER JOIN `user_skill_associations` ON (`users`.`id` = `user_skill_associations`.`user_id`)  LEFT OUTER JOIN `skills` ON (`skills`.`id` = `user_skill_associations`.`skill_id`)   LEFT OUTER JOIN `user_branch_associations` ON (`users`.`id` = `user_branch_associations`.`user_id`)  LEFT OUTER JOIN `branches` ON (`branches`.`id` = `user_branch_associations`.`branch_id`)   LEFT OUTER JOIN `realms` ON `realms`.id = `users`.realm_id  WHERE `users`.`id` >= $start AND `users`.`id` <= $end AND `users`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND) AND state = 'active' GROUP BY `users`.`id`  ORDER BY NULL
  sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `users` WHERE `users`.`updated_at` > DATE_SUB(NOW(), INTERVAL 600 SECOND)
  sql_attr_uint = sphinx_internal_id
  sql_attr_uint = class_crc
  sql_attr_uint = sphinx_deleted
  sql_attr_uint = user_id
  sql_attr_uint = roles
  sql_attr_uint = city_id
  sql_attr_uint = realm_id
  sql_attr_uint = rate
  sql_attr_uint = min_price
  sql_attr_uint = finished_projects_count
  sql_attr_uint = review_rating
  sql_attr_str2ordinal = name_sort
  sql_attr_multi = uint subclass_crcs from field
  sql_attr_multi = uint branch_ids from field
  sql_attr_multi = uint skill_ids from field
  sql_query_info = SELECT * FROM `users` WHERE `id` = (($id - 4) / 5)
}

index user_delta : user_core
{
  source = user_delta_0
  path = /home/danil/src/vendor/prohq/avers/db/sphinx/development/user_delta
}

index user
{
  type = distributed
  local = user_delta
  local = user_core
}

Gems

gem list:

*** LOCAL GEMS ***
actionmailer (2.3.5)
actionpack (2.3.5)
activemodel (3.0.0)
activerecord (2.3.5)
activeresource (2.3.5)
activesupport (3.0.0, 2.3.9, 2.3.5)
after_commit (1.0.8)
arel (1.0.1)
builder (2.1.2)
bundler (1.0.0)
capistrano (2.5.19)
capistrano-ext (1.2.1)
cgi_multipart_eof_fix (2.5.0)
columnize (0.3.1)
contacts (1.2.3)
daemons (1.1.0)
delayed_job (1.8.4)
fastthread (1.0.7)
gdata (1.1.1)
gem_plugin (0.2.3)
highline (1.6.1)
hominid (2.1.5)
hpricot (0.8.2)
i18n (0.4.1)
json (1.4.6)
linecache (0.43)
mongrel (1.1.5)
mysql (2.8.1)
net-scp (1.0.4)
net-sftp (2.0.5)
net-ssh (2.0.23)
net-ssh-gateway (1.0.1)
rack (1.0.1)
rails (2.3.5)
rake (0.8.7)
RedCloth (4.2.3)
riddle (1.1.0)
ruby-debug (0.10.3)
ruby-debug-base (0.10.3)
russian (0.2.7)
thinking-sphinx (1.3.15)
ts-datetime-delta (1.0.2)
tzinfo (0.3.23)

Ruby

rvm info:

ruby-1.8.7-p302@prohq:

  system:
    uname:       "Linux frink 2.6.34-gentoo-r6 #25 SMP Sat Sep 25 10:37:32 MSD 2010 x86_64 Intel(R) Core(TM)2 Duo CPU P8600 @ 2.40GHz GenuineIntel GNU/Linux"
    bash:        "/bin/bash => GNU bash, version 4.0.37(2)-release (x86_64-pc-linux-gnu)"
    zsh:         " => not installed"

  rvm:
    version:      "rvm 1.0.10 by Wayne E. Seguin (wayneeseguin@gmail.com) [http://rvm.beginrescueend.com/]"

  ruby:
    interpreter:  "ruby"
    version:      "1.8.7"
    date:         "2010-08-16"
    platform:     "x86_64-linux"
    patchlevel:   "2010-08-16 patchlevel 302"
    full_version: "ruby 1.8.7 (2010-08-16 patchlevel 302) [x86_64-linux]"

  homes:
    gem:          "/home/danil/.rvm/gems/ruby-1.8.7-p302@prohq"
    ruby:         "/home/danil/.rvm/rubies/ruby-1.8.7-p302"

  binaries:
    ruby:         "/home/danil/.rvm/rubies/ruby-1.8.7-p302/bin/ruby"
    irb:          "/home/danil/.rvm/rubies/ruby-1.8.7-p302/bin/irb"
    gem:          "/home/danil/.rvm/rubies/ruby-1.8.7-p302/bin/gem"
    rake:         "/home/danil/.rvm/gems/ruby-1.8.7-p302@global/bin/rake"

  environment:
    PATH:         "/home/danil/bin:/home/danil/.rvm/gems/ruby-1.8.7-p302@prohq/bin:/home/danil/.rvm/gems/ruby-1.8.7-p302@global/bin:/home/danil/.rvm/rubies/ruby-1.8.7-p302/bin:/home/danil/.rvm/bin:/usr/local/bin:/usr/bin:/bin:/opt/bin:/usr/x86_64-pc-linux-gnu/gcc-bin/4.4.3:/usr/games/bin"
    GEM_HOME:     "/home/danil/.rvm/gems/ruby-1.8.7-p302@prohq"
    GEM_PATH:     "/home/danil/.rvm/gems/ruby-1.8.7-p302@prohq:/home/danil/.rvm/gems/ruby-1.8.7-p302@global"
    BUNDLE_PATH:  "/home/danil/.rvm/gems/ruby-1.8.7-p302@prohq"
    MY_RUBY_HOME: "/home/danil/.rvm/rubies/ruby-1.8.7-p302"
    IRBRC:        "/home/danil/.rvm/rubies/ruby-1.8.7-p302/.irbrc"
    RUBYOPT:      ""
    gemset:       "prohq"

System

uname -a:

Linux frink 2.6.34-gentoo-r6 #25 SMP Sat Sep 25 10:37:32 MSD 2010 x86_64 Intel(R) Core(TM)2 Duo CPU P8600 @ 2.40GHz GenuineIntel GNU/Linux

emerge --info:

Portage 2.1.8.3 (default/linux/amd64/10.0, gcc-4.4.3, glibc-2.11.2-r0, 2.6.34-gentoo-r6 x86_64)
=================================================================
System uname: Linux-2.6.34-gentoo-r6-x86_64-Intel-R-_Core-TM-2_Duo_CPU_P8600_@_2.40GHz-with-gentoo-1.12.13
Timestamp of tree: Fri, 08 Oct 2010 06:30:01 +0000
ccache version 2.4 [enabled]
app-shells/bash:     4.0_p37
dev-lang/python:     2.6.5-r3, 3.1.2-r4
dev-util/ccache:     2.4-r7
dev-util/cmake:      2.8.1-r2
sys-apps/baselayout: 1.12.13
sys-apps/sandbox:    2.3-r1
sys-devel/autoconf:  2.13, 2.65-r1
sys-devel/automake:  1.10.3, 1.11.1
sys-devel/binutils:  2.20.1-r1
sys-devel/gcc:       4.4.3-r2
sys-devel/gcc-config: 1.4.1
sys-devel/libtool:   2.2.10
sys-devel/make:      3.81-r2
virtual/os-headers:  2.6.30-r1
ACCEPT_KEYWORDS="amd64"
ACCEPT_LICENSE="* -@EULA"
CBUILD="x86_64-pc-linux-gnu"
CFLAGS="-O2 -pipe -fomit-frame-pointer -march=core2"
CHOST="x86_64-pc-linux-gnu"
CONFIG_PROTECT="/boot /etc /home /usr/lib/urxvt/perl /usr/share/X11/xkb"
CONFIG_PROTECT_MASK="/etc/ca-certificates.conf /etc/eselect/postgresql /etc/fonts/fonts.conf /etc/gconf /etc/revdep-rebuild /etc/sandbox.d /etc/terminfo"
CXXFLAGS="-O2 -pipe -fomit-frame-pointer -march=core2"
DISTDIR="/usr/portage/distfiles"
FEATURES="assume-digests ccache distlocks fixpackages news parallel-fetch protect-owned sandbox sfperms strict unmerge-logs unmerge-orphans userfetch"
GENTOO_MIRRORS="ftp://mirror.yandex.ru/gentoo-distfiles/ http://mirror.yandex.ru/gentoo-distfiles/ http://distfiles.gentoo.org"
LANG="en_US.UTF-8"
LDFLAGS="-Wl,-O1 -Wl,--as-needed"
LINGUAS="en_US en ru_RU ru"
MAKEOPTS="-j4"
PKGDIR="/usr/portage/packages"
PORTAGE_CONFIGROOT="/"
PORTAGE_RSYNC_OPTS="--recursive --links --safe-links --perms --times --compress --force --whole-file --delete --stats --timeout=180 --exclude=/distfiles --exclude=/local --exclude=/packages"
PORTAGE_TMPDIR="/var/tmp"
PORTDIR="/usr/portage"
PORTDIR_OVERLAY="/var/lib/layman/lisp /var/lib/layman/kutkevich /var/lib/layman/sunrise"
SYNC="rsync://rsync.gentoo.org/gentoo-portage"
USE="X acl acpi alsa amd64 apm bash-completion berkdb branding bzip2 cairo cli cracklib crypt cups cxx dbus djvu dri ffmpeg flac fortran frink gdbm gif gpm grammar gstreamer hal iceweasel iconv ipv6 jpeg laptop mad midi mmx modules mp3 mpeg mudflap multilib ncurses nls nptl nptlonly ogg opengl openmp pam pcre pdf perl pmu png pppd python readline reflection session spell sse sse2 sse3 ssl svg sysfs tcpd tiff unicode userlocales v4l v4l2 vorbis wifi xorg xpm xv zlib" ALSA_CARDS="ali5451 als4000 atiixp atiixp-modem bt87x ca0106 cmipci emu10k1x ens1370 ens1371 es1938 es1968 fm801 hda-intel intel8x0 intel8x0m maestro3 trident usb-audio via82xx via82xx-modem ymfpci" ALSA_PCM_PLUGINS="adpcm alaw asym copy dmix dshare dsnoop empty extplug file hooks iec958 ioplug ladspa lfloat linear meter mmap_emul mulaw multi null plug rate route share shm softvol" APACHE2_MODULES="actions alias auth_basic authn_alias authn_anon authn_dbm authn_default authn_file authz_dbm authz_default authz_groupfile authz_host authz_owner authz_user autoindex cache cgi cgid dav dav_fs dav_lock deflate dir disk_cache env expires ext_filter file_cache filter headers include info log_config logio mem_cache mime mime_magic negotiation rewrite setenvif speling status unique_id userdir usertrack vhost_alias" COLLECTD_PLUGINS="df interface irq load memory rrdtool swap syslog" ELIBC="glibc" GPSD_PROTOCOLS="ashtech aivdm earthmate evermore fv18 garmin garmintxt gpsclock itrax mtk3301 nmea ntrip navcom oceanserver oldstyle oncore rtcm104v2 rtcm104v3 sirf superstar2 timing tsip tripmate tnt ubx" INPUT_DEVICES="evdev keyboard mouse synaptics" KERNEL="linux" LCD_DEVICES="bayrad cfontz cfontz633 glk hd44780 lb216 lcdm001 mtxorb ncurses text" LINGUAS="en_US en ru_RU ru" RUBY_TARGETS="ruby18" USERLAND="GNU" VIDEO_CARDS="radeon intel vesa" XTABLES_ADDONS="quota2 psd pknock lscan length2 ipv4options ipset ipp2p iface geoip fuzzy condition tee tarpit sysrq steal rawnat logmark ipmark dhcpmac delude chaos account" 
Unset:  CPPFLAGS, CTARGET, EMERGE_DEFAULT_OPTS, FFLAGS, INSTALL_MASK, LC_ALL, PORTAGE_COMPRESS, PORTAGE_COMPRESS_FLAGS, PORTAGE_RSYNC_EXTRA_OPTS
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment