{"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":"http:\/\/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 decoding=\"async\" loading=\"lazy\" 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<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-email\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-email sd-button share-icon\" href=\"mailto:?subject=%5BShared%20Post%5D%20How%20to%20offload%20MySQL%20server%20with%20Sphinx%20by%20Vladimir%20Fedorkov&body=http%3A%2F%2Fwww.bytebot.net%2Fblog%2Farchives%2F2012%2F02%2F05%2Fhow-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov&share=email\" target=\"_blank\" title=\"Click to email a link to a friend\" data-email-share-error-title=\"Do you have email set up?\" data-email-share-error-text=\"If you&#039;re having problems sharing via email, you might not have email set up for your browser. You may need to create a new email yourself.\" data-email-share-nonce=\"b50f4f49f2\" data-email-share-track-url=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=email\"><span>Email<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-2259\" class=\"share-facebook sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=facebook\" target=\"_blank\" title=\"Click to share on Facebook\" ><span>Facebook<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-2259\" class=\"share-linkedin sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\" ><span>LinkedIn<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-2259\" class=\"share-twitter sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\" ><span>Twitter<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div>","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<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-email\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-email sd-button share-icon\" href=\"mailto:?subject=%5BShared%20Post%5D%20How%20to%20offload%20MySQL%20server%20with%20Sphinx%20by%20Vladimir%20Fedorkov&body=http%3A%2F%2Fwww.bytebot.net%2Fblog%2Farchives%2F2012%2F02%2F05%2Fhow-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov&share=email\" target=\"_blank\" title=\"Click to email a link to a friend\" data-email-share-error-title=\"Do you have email set up?\" data-email-share-error-text=\"If you&#039;re having problems sharing via email, you might not have email set up for your browser. You may need to create a new email yourself.\" data-email-share-nonce=\"b50f4f49f2\" data-email-share-track-url=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=email\"><span>Email<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-2259\" class=\"share-facebook sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=facebook\" target=\"_blank\" title=\"Click to share on Facebook\" ><span>Facebook<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-2259\" class=\"share-linkedin sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\" ><span>LinkedIn<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-2259\" class=\"share-twitter sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/how-to-offload-mysql-server-with-sphinx-by-vladimir-fedorkov?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\" ><span>Twitter<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_options":[]},"categories":[23],"tags":[1332,1335,1341],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4vJD-Ar","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":791,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2008\/04\/15\/mysql-full-text-search-by-alex-rubin","url_meta":{"origin":2259,"position":0},"title":"MySQL Full Text Search by Alex Rubin","date":"15\/4\/2008","format":false,"excerpt":"Download the PDF: http:\/\/www.mysqlfulltextsearch.com\/full_text.pdfDefault search by relevance, default sort is by relevanceBoolean search is also popular. cats AND dogs. No default sorting, so you need to order the results yourselfPhrase searchMySQL Full Text Index, only available with MyISAM, and it supports natural language and Boolean search. ft_min_word_len - 4 characters\u2026","rel":"","context":"In &quot;MySQL&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2832,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2013\/11\/30\/groonga-fulltext-search-library-for-cloud-web","url_meta":{"origin":2259,"position":1},"title":"groonga &#8211; fulltext search library for cloud &#038; web","date":"30\/11\/2013","format":false,"excerpt":"This is an incomplete fragment from 2011. Figure its worth publishing this now, considering MariaDB is likely to get groonga in the near future. The groonga team have released MariaDB 10.0.6 binaries as well. This is all part of the mroonga\u00a0project. These were my quick notes from the groonga talk\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":706,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2008\/01\/28\/morning-sessions-at-mysql-miniconf","url_meta":{"origin":2259,"position":2},"title":"Morning sessions at MySQL MiniConf","date":"28\/1\/2008","format":false,"excerpt":"Upcoming MySQL Features - Stewart SmithStewart's talk on Upcoming MySQL Features was sort of a roadmap of what one might expect to see in MySQL 5.1 and above - he touched on Falcon, online backup, batched key access, Maria, Proxy, Workbench, and some cluster changes. When he shares his slides,\u2026","rel":"","context":"In &quot;MySQL&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2250,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/sphinx-user-stories-by-stephane-varoqui","url_meta":{"origin":2259,"position":3},"title":"Sphinx user stories by St\u00c3\u00a9phane Varoqui","date":"5\/2\/2012","format":false,"excerpt":"Stephane Varoqui, Field Services SkySQL, Vlad Fedorkov, Director of PS, Sphinx Inc, Christophe Gesche, LAMP Expert, Delcampe, Herve Seignole, Web Architect, Groupe Pierre & Vacances Center Parcs - this is a big talk! Pros: Filtering takes place on attributes in separate tables. Rely on the optimizer choice. HASH JOIN can\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2348,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2012\/03\/25\/more-mariadb-after-percona-live-santa-clara","url_meta":{"origin":2259,"position":4},"title":"More MariaDB after Percona Live Santa Clara","date":"25\/3\/2012","format":false,"excerpt":"Right after Percona Live Santa Clara (which MariaDB is quite present for), its worth noting there are a few more events happening on Friday, April 13, 2012 at the Hyatt Regency Santa Clara. MariaDB will be present at 2\/3 of those events. SkySQL & MariaDB Solutions Day - go ahead\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2143,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2011\/05\/26\/the-skysql-reference-architecture","url_meta":{"origin":2259,"position":5},"title":"The SkySQL Reference Architecture","date":"26\/5\/2011","format":false,"excerpt":"I have a bunch of notes from the O'Reilly MySQL Conference & Expo 2011, and I figure its about time I started blogging it. These are notes from the panel on the SkySQL Reference Architecture, led by Kaj Arno and Ivan Zoratti. The notes are raw (read their FAQ for\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"amp_enabled":true,"_links":{"self":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2259"}],"collection":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/comments?post=2259"}],"version-history":[{"count":4,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2259\/revisions"}],"predecessor-version":[{"id":2276,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2259\/revisions\/2276"}],"wp:attachment":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/media?parent=2259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/categories?post=2259"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/tags?post=2259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}