{"id":2259,"date":"2012-02-05T05:29:05","date_gmt":"2012-02-05T10:29:05","guid":{"rendered":"http:\/\/www.bytebot.net\/blog\/?p=2259"},"modified":"2012-02-05T06:31:10","modified_gmt":"2012-02-05T11:31:10","slug":"how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov","status":"publish","type":"post","link":"https:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov","title":{"rendered":"How to offload MySQL server with Sphinx by Vladimir Fedorkov"},"content":{"rendered":"<p>Vladimir Fedorkov of <a href=\"http:\/\/sphinxsearch.com\/\">Sphinx<\/a>.<\/p>\n<p>Presentation started out with a very nice presentation of candies to all the audience members.<\/p>\n<p>What is Sphinx? Another (C++) daemon on your boxes. Can be queried via API (PHP, Python, etc.) or MySQL-compatible protocol and SQL queries (SphinxQL). Some query examples are in the slides, here&#8217;s one about <a href=\"http:\/\/kb.askmonty.org\/en\/about-sphinxse\">SphinxSE in the KB<\/a>.<\/p>\n<p>MyISAM FTS is good but becomes slow with half a million documents. InnoDB has FTS now but he&#8217;s not tried it (and neither has anyone in the audience to see it compare with MyISAM FTS).<\/p>\n<p>Geographical distance is the distance measuring the surface of the earth (two pairs of float values &#8211; latitude, longitude). In Sphinx, there is support for GEODIST(Lat,Long,Lat2,Long2) in Sphinx.<\/p>\n<p>Segments are good for price ranges on a site, date ranges, etc. Use INTERVAL(field, x0, x1, \u2026, xN).<\/p>\n<p>Keep huge text collections out of the database. sql_field = path_to_file_text. Tell Sphinx to index text not from MySQL but out in the filesystem. Keep the metadata inside the database but keep the actual data outside of the database. max_file_field_buffer needs to be set properly.<\/p>\n<p>You can do proximity search with Sphinx &#8212; find the words &#8220;hello world&#8221; within a ten word block, for example.<\/p>\n<p>Resources: the <a href=\"http:\/\/sphinxsearch.com\/docs\/\">documentation<\/a>, a book by O&#8217;Reilly: <a href=\"http:\/\/www.amazon.com\/gp\/product\/0596809557\/ref=as_li_ss_tl?ie=UTF8&amp;tag=colincharles-20&amp;linkCode=as2&amp;camp=1789&amp;creative=390957&amp;creativeASIN=0596809557\">Introduction to Search with Sphinx: From installation to relevance tuning<\/a><img loading=\"lazy\" decoding=\"async\" style=\"border: none !important; margin: 0px !important;\" src=\"http:\/\/www.assoc-amazon.com\/e\/ir?t=colincharles-20&amp;l=as2&amp;o=1&amp;a=0596809557\" border=\"0\" alt=\"\" width=\"1\" height=\"1\" \/> (sold out at the FOSDEM O&#8217;Reilly booth!), and their <a href=\"http:\/\/sphinxsearch.com\/community\/\">community<\/a> page including wiki, forum, etc.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Vladimir Fedorkov of Sphinx. Presentation started out with a very nice presentation of candies to all the audience members. What is Sphinx? Another (C++) daemon on your boxes. Can be queried via API (PHP, Python, etc.) or MySQL-compatible protocol and SQL queries (SphinxQL). Some query examples are in the slides, here&#8217;s one about SphinxSE in [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[23],"tags":[1332,1335,1341],"class_list":["post-2259","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-fosdem","tag-sphinx","tag-vladimir-fedorkov"],"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p4vJD-Ar","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/comments?post=2259"}],"version-history":[{"count":4,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2259\/revisions"}],"predecessor-version":[{"id":2276,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2259\/revisions\/2276"}],"wp:attachment":[{"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/media?parent=2259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/categories?post=2259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/tags?post=2259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}