Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 8th, 2012  |  syntax: None  |  size: 5.29 KB  |  hits: 16  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Ideas to decrease search time in Sphinx
  2. define_index do
  3.   indexes :name
  4.   indexes :summary
  5.   indexes :tag_list
  6.  
  7.   indexes categories.name, :as => :category_name
  8.  
  9.   has "RADIANS(lat)",  :as => :latitude,  :type => :float
  10.   has "RADIANS(lng)",  :as => :longitude,  :type => :float
  11.  
  12.   set_property :field_weights => {
  13.     :name           => 8,
  14.     :summary        => 6,
  15.     :category_name  => 5,
  16.     :tag_list       => 3
  17.   }
  18.   set_property :delta => ThinkingSphinx::Deltas::ResqueDelta
  19.   set_property :ignore_chars => %w(' -)
  20. end
  21.        
  22. Location.search('Restaurant',
  23.                 :geo => [0.5837843098436726,-1.9560609568879357],
  24.                 :latitude_attr => "latitude",
  25.                 :longitude_attr => "longitude",
  26.                 :with => {"@geodist" => 0.0..4000.0},
  27.                 :include => :categories,
  28.                 :page => 1,
  29.                 :per_page => 100)
  30.        
  31. Sphinx Query (43066.3ms)  restaurant
  32. Sphinx  Found 467 results
  33.        
  34. indexer
  35. {
  36. }
  37.  
  38. searchd
  39. {
  40.     listen = 127.0.0.1:9312
  41.     log = /project_path/log/searchd.log
  42.     query_log = /project_path/log/searchd.query.log
  43.     pid_file = /project_path/log/searchd.development.pid
  44. }
  45.  
  46. source location_core_0
  47. {
  48.     type = pgsql
  49.     sql_host = localhost
  50.     sql_user = user
  51.     sql_pass = pass
  52.     sql_db = db_name
  53.     sql_query_pre = UPDATE "business_entities" SET "delta" = FALSE WHERE "delta" = TRUE
  54.     sql_query_pre = SET TIME ZONE 'UTC'
  55.     sql_query = SELECT "business_entities"."id" * 1::INT8 + 0 AS "id" , "business_entities"."name" AS "name", "business_entities"."summary" AS "summary", "business_entities"."tag_list" AS "tag_list", "business_entities"."id" AS "sphinx_internal_id", 0 AS "sphinx_deleted", CASE COALESCE("business_entities"."type", '') WHEN 'Location' THEN 2817059741 WHEN 'Group' THEN 2885774273 WHEN 'BraintreeBusiness' THEN 28779289 WHEN 'InvoicedBusiness' THEN 1440117572 ELSE 2817059741 END AS "class_crc", COALESCE("business_entities"."type", '') AS "sphinx_internal_class", RADIANS(lat) AS "latitude", RADIANS(lng) AS "longitude" FROM "business_entities" WHERE ("business_entities"."type" = 'Location') AND ("business_entities"."id" >= $start AND "business_entities"."id" <= $end AND "business_entities"."delta" = FALSE AND "business_entities"."type" = 'Location') GROUP BY "business_entities"."id", "business_entities"."name", "business_entities"."summary", "business_entities"."tag_list", "business_entities"."id", "business_entities"."type"
  56.     sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint), COALESCE(MAX("id"), 1::bigint) FROM "business_entities" WHERE "business_entities"."delta" = FALSE
  57.     sql_attr_uint = sphinx_internal_id
  58.     sql_attr_uint = sphinx_deleted
  59.     sql_attr_uint = class_crc
  60.     sql_attr_float = latitude
  61.     sql_attr_float = longitude
  62.     sql_attr_string = sphinx_internal_class
  63.     sql_query_info = SELECT * FROM "business_entities" WHERE "id" = (($id - 0) / 1)
  64. }
  65.  
  66. index location_core
  67. {
  68.     source = location_core_0
  69.     path = /project_path/db/sphinx/development/location_core
  70.     morphology = stem_en
  71.     charset_type = utf-8
  72.     ignore_chars = ', -
  73.     enable_star = 1
  74. }
  75.  
  76. source location_delta_0 : location_core_0
  77. {
  78.     type = pgsql
  79.     sql_host = localhost
  80.     sql_user = user
  81.     sql_pass = pass
  82.     sql_db = db_name
  83.     sql_query_pre =
  84.     sql_query_pre = SET TIME ZONE 'UTC'
  85.     sql_query = SELECT "business_entities"."id" * 1::INT8 + 0 AS "id" , "business_entities"."name" AS "name", "business_entities"."summary" AS "summary", "business_entities"."tag_list" AS "tag_list", "business_entities"."id" AS "sphinx_internal_id", 0 AS "sphinx_deleted", CASE COALESCE("business_entities"."type", '') WHEN 'Location' THEN 2817059741 WHEN 'Group' THEN 2885774273 WHEN 'BraintreeBusiness' THEN 28779289 WHEN 'InvoicedBusiness' THEN 1440117572 ELSE 2817059741 END AS "class_crc", COALESCE("business_entities"."type", '') AS "sphinx_internal_class", RADIANS(lat) AS "latitude", RADIANS(lng) AS "longitude" FROM "business_entities" WHERE ("business_entities"."type" = 'Location') AND ("business_entities"."id" >= $start AND "business_entities"."id" <= $end AND "business_entities"."delta" = TRUE AND "business_entities"."type" = 'Location') GROUP BY "business_entities"."id", "business_entities"."name", "business_entities"."summary", "business_entities"."tag_list", "business_entities"."id", "business_entities"."type"
  86.     sql_query_range = SELECT COALESCE(MIN("id"), 1::bigint), COALESCE(MAX("id"), 1::bigint) FROM "business_entities" WHERE "business_entities"."delta" = TRUE
  87.     sql_attr_uint = sphinx_internal_id
  88.     sql_attr_uint = sphinx_deleted
  89.     sql_attr_uint = class_crc
  90.     sql_attr_float = latitude
  91.     sql_attr_float = longitude
  92.     sql_attr_string = sphinx_internal_class
  93.     sql_query_info = SELECT * FROM "business_entities" WHERE "id" = (($id - 0) / 1)
  94. }
  95.  
  96. index location_delta : location_core
  97. {
  98.     source = location_delta_0
  99.     path = /project_path/db/sphinx/development/location_delta
  100. }
  101.  
  102. index location
  103. {
  104.     type = distributed
  105.     local = location_delta
  106.     local = location_core
  107. }
  108.        
  109. Location.search('Restaurant')
  110.        
  111. Location.search('Restaurant', :per_page => 100)
  112.        
  113. SQL (43647.1ms)  SELECT DISTINCT type FROM business_entities
  114. SQL (39857.7ms)  SELECT DISTINCT type FROM business_entities
  115.  
  116. Sphinx Query (84173.0ms)  restaurant
  117.        
  118. module ThinkingSphinx
  119.   class Source
  120.     module SQL
  121.       def type_values
  122.         ['Location']
  123.       end
  124.     end
  125.   end
  126. end
create a new version of this paste RAW Paste Data