CINXE.COM

<!DOCTYPE html> <html lang="en"> <head><script type="text/javascript" src="/_static/js/bundle-playback.js?v=HxkREWBo" charset="utf-8"></script> <script type="text/javascript" src="/_static/js/wombat.js?v=txqj7nKC" charset="utf-8"></script> <script>window.RufflePlayer=window.RufflePlayer||{};window.RufflePlayer.config={"autoplay":"on","unmuteOverlay":"hidden"};</script> <script type="text/javascript" src="/_static/js/ruffle/ruffle.js"></script> <script type="text/javascript"> __wm.init("https://web.archive.org/web"); __wm.wombat("http://dev.mysql.com:80/doc/refman/4.1/en/fulltext-search.html","20130111042031","https://web.archive.org/","web","/_static/", "1357878031"); </script> <link rel="stylesheet" type="text/css" href="/_static/css/banner-styles.css?v=S1zqJCYt" /> <link rel="stylesheet" type="text/css" href="/_static/css/iconochive.css?v=3PDvdIFv" />  <meta charset="utf-8"/>  <title>MySQL :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 11.9 Full-Text Search Functions</title> <link rel="stylesheet" media="screen" href="/web/20130111042031cs_/http://dev.mysql.com/common/css/mysql.css?v=20121230"/> <link rel="stylesheet" media="projection" href="/web/20130111042031cs_/http://dev.mysql.com/common/css/mysql.css?v=20121230"/> <link rel="stylesheet" media="print" href="/web/20130111042031cs_/http://dev.mysql.com/common/css/print.css?v=20111230"/> <link rel="stylesheet" href="/web/20130111042031cs_/http://dev.mysql.com/doc/docs.css"/> <link rel="contents" href="index.html" title="MySQL Manual"/><link rel="start" href="index.html" title="MySQL Manual"/><link rel="prev" href="mysql-calendar.html" title="11.8 What Calendar Is Used By MySQL?"/><link rel="next" href="fulltext-natural-language.html?ff=nopfpls" title="11.9.1 Natural Language Full-Text Searches"/><link rel="up" href="functions.html" title="11 Functions and Operators"/> <link rel="shortcut icon" href="/web/20130111042031im_/http://dev.mysql.com/common/themes/sakila/favicon.ico"/> <script src="/web/20130111042031js_/http://dev.mysql.com/common/js/clear_search_text.js"></script> <script type="text/javascript">var addthis_config = {"data_track_addressbar":false};</script>  <script type="text/javascript" src="//web.archive.org/web/20130111042031js_/http://s7.addthis.com/js/250/addthis_widget.js#pubid=ra-4fc7ca0b3e7d0a70"></script> </head> <body class="doc"> <div id="container">  <a class="skipToContent" href="#mainContent">Skip navigation links</a> <div id="header"> <div class="left"> <div id="logo"> <a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/" title="MySQL"> <img src="/web/20130111042031im_/http://dev.mysql.com/common/logos/logo-mysql-110x57.png" alt="MySQL" width="110" height="57"/></a> </div> <div id="tagline">The world's most popular open source database</div> </div> <div class="right"> <div id="search_box">  <form id="searchform" name="searchform" method="get" action="https://web.archive.org/web/20130111042031/http://search.oracle.com/search/search"> <input type="text" id="q" name="q" value="Search" class="swap_value" onfocus="clearSearchText();"/> <input type="hidden" id="search_dest" name="group" value="Documentation"/> <input type="image" src="/web/20130111042031im_/http://dev.mysql.com/common/themes/sakila/search_g.png" id="go" alt="Search" title="Search"/> </form> </div>  <div id="login"> <p><a href="https://web.archive.org/web/20130111042031/https://dev.mysql.com/auth/login/?dest=http%3a%2f%2fdev.mysql.com%2Fdoc%2Frefman%2F4.1%2Fen%2Ffulltext-search.html">Login</a> | <a href="https://web.archive.org/web/20130111042031/https://dev.mysql.com/auth/register/">Register</a></p> </div> </div> </div>  <div id="nav_container"> <div id="flags"> <ul> <li style="position:relative; top:-5px;">  <a href="https://web.archive.org/web/20130111042031/http://www.facebook.com/mysql"><img src="/web/20130111042031im_/http://dev.mysql.com/common/icons/facebook.png" alt="Facebook" title="Join us on Facebook" width="20" height="20"/></a>  <a href="https://web.archive.org/web/20130111042031/https://twitter.com/#!/mysql"><img src="/web/20130111042031im_/http://dev.mysql.com/common/icons/twitter.png" alt="Twitter" title="Follow us on Twitter" width="20" height="20"/></a>  <a href="https://web.archive.org/web/20130111042031/http://www.youtube.com/mysqlchannel"><img src="/web/20130111042031im_/http://dev.mysql.com/common/icons/youtube.png" alt="YouTube" title="Visit our YouTube channel" width="20" height="20"/></a>       </li> <br/> </ul> </div> <div id="tab_last"> <ul> <li class="first"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/">Developer Zone</a></li> <li class="second"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/">Downloads</a></li> <li class="current"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/">Documentation</a></li> </ul> </div> <div id="mysql_menu"> <ul> <li class="current"><a class="current " href="/web/20130111042031/http://dev.mysql.com/doc/">MySQL Server</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-enterprise.html">MySQL Enterprise</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-gui.html">MySQL Workbench</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-cluster.html">MySQL Cluster</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-connectors.html">MySQL Connectors</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-topic.html">Topic Guides</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-expert.html">Expert Guides</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-other.html">Other Docs</a> </li> <li class="link"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-archive.html">Archives</a> </li> <li class="link last"><a href="/web/20130111042031/http://dev.mysql.com/doc/index-about.html">About</a> </li> </ul> </div> <div id="og_sakila"> </div> <div id="og_title"> </div> </div>   <div class="page_container"> <div class="page_sidebar">  <div id="menu_title"></div> <div id="menu"> <ul> <li class="current"><a class="current subitems" href="/web/20130111042031/http://dev.mysql.com/doc/index.html">Documentation Library</a> <ul class="subitems1"> <li><a href="index.html"><b>Table of Contents</b></a> <ul class="subitems2"> <li> <a href="/web/20130111042031/http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html">MySQL 5.6 Manual</a> </li> <li> <a href="/web/20130111042031/http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html">MySQL 5.5 Manual</a> </li> <li> <a href="/web/20130111042031/http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html">MySQL 5.1 Manual</a> </li> <li> <a href="/web/20130111042031/http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html">MySQL 5.0 Manual</a> </li> <li class="current">MySQL 3.23/4.0/4.1 Manual </li> </ul> </li> </ul> </li> </ul> <div class="searchmanual"> <form action="https://web.archive.org/web/20130111042031/http://search.oracle.com/search/search" method="get" id="docsearch"> <strong><label for="searchq">Search manual:</label></strong> <br/> <input id="searchq" type="text" name="q" value="" size="14"/> <input id="searchsubmit" type="submit" value="Go"/> <input id="searchgroup" type="hidden" name="group" value="MySQL"/> </form> </div> </div> </div>  <div id="page" class="sidebar">  <div id="docheader"><a href="index.html"> MySQL 3.23, 4.0, 4.1 Reference Manual</a> :: <a href="functions.html">11 Functions and Operators</a> :: 11.9 Full-Text Search Functions</div> <script language="javascript">  </script> <noscript></noscript> <div style="float: right; width: 250px; margin: 0px 0px 8px 8px; background: white"> <div style="text-align: right; font-size: 90%; margin-bottom: 4px"> <div style="text-align: left;"><a style="text-decoration: none" href="mysql-calendar.html" title="Previous Section">« 11.8 What Calendar Is Used By MySQL?</a></div> <hr size="1" noshade="noshade" align="center" width="40" style="margin: 0px auto 2px auto"/> <a style="text-decoration: none" href="fulltext-natural-language.html" title="Next Section">11.9.1 Natural Language Full-Text Searches »</a> </div> <div style="padding: 4px 0px 0px 4px; border: 2px dotted #ccc; border-right: none;"> <b>Section Navigation</b>      <small>[<a href="#" onclick="toggle('sectionnav');" onkeypress="if (event.keyCode == 13) { toggle('sectionnav'); }">Toggle</a>]</small> <ul id="sectionnav"> <li class="p"><a href="functions.html">11 Functions and Operators</a></li><li><a href="func-op-summary-ref.html">11.1 Function and Operator Reference</a></li> <li><a href="type-conversion.html">11.2 Type Conversion in Expression Evaluation</a></li> <li><a href="non-typed-operators.html">11.3 Operators</a></li> <li><a href="control-flow-functions.html">11.4 Control Flow Functions</a></li> <li><a href="string-functions.html">11.5 String Functions</a></li> <li><a href="numeric-functions.html">11.6 Numeric Functions and Operators</a></li> <li><a href="date-and-time-functions.html">11.7 Date and Time Functions</a></li> <li><a href="mysql-calendar.html">11.8 What Calendar Is Used By MySQL?</a></li> <li class="self">11.9 Full-Text Search Functions <ul class="children"> <li><a href="fulltext-natural-language.html">11.9.1 Natural Language Full-Text Searches</a></li> <li><a href="fulltext-boolean.html">11.9.2 Boolean Full-Text Searches</a></li> <li><a href="fulltext-query-expansion.html">11.9.3 Full-Text Searches with Query Expansion</a></li> <li><a href="fulltext-stopwords.html">11.9.4 Full-Text Stopwords</a></li> <li><a href="fulltext-restrictions.html">11.9.5 Full-Text Restrictions</a></li> <li><a href="fulltext-fine-tuning.html">11.9.6 Fine-Tuning MySQL Full-Text Search</a></li> </ul> </li> <li><a href="cast-functions.html">11.10 Cast Functions and Operators</a></li> <li><a href="bit-functions.html">11.11 Bit Functions</a></li> <li><a href="encryption-functions.html">11.12 Encryption and Compression Functions</a></li> <li><a href="information-functions.html">11.13 Information Functions</a></li> <li><a href="miscellaneous-functions.html">11.14 Miscellaneous Functions</a></li> <li><a href="group-by-functions-and-modifiers.html">11.15 Functions and Modifiers for Use with GROUP BY Clauses</a></li> </ul> </ul> </div> </div>  <div class="section" lang="en"> <div class="titlepage"><div><div><h2 class="title"> <a name="fulltext-search"></a>11.9. Full-Text Search Functions</h2></div></div></div> <div class="toc"><p><small>[<a href="#" class="tocdetail" onclick="toggle('tocdetail-0');" onkeypress="if (event.keyCode == 13) { toggle('tocdetail-0');}">+/-</a>]</small></p><dl id="tocdetail-0"> <dt><span class="section"><a href="fulltext-natural-language.html">11.9.1. Natural Language Full-Text Searches</a></span></dt> <dt><span class="section"><a href="fulltext-boolean.html">11.9.2. Boolean Full-Text Searches</a></span></dt> <dt><span class="section"><a href="fulltext-query-expansion.html">11.9.3. Full-Text Searches with Query Expansion</a></span></dt> <dt><span class="section"><a href="fulltext-stopwords.html">11.9.4. Full-Text Stopwords</a></span></dt> <dt><span class="section"><a href="fulltext-restrictions.html">11.9.5. Full-Text Restrictions</a></span></dt> <dt><span class="section"><a href="fulltext-fine-tuning.html">11.9.6. Fine-Tuning MySQL Full-Text Search</a></span></dt> </dl></div> <a class="indexterm" name="id575850"></a><a class="indexterm" name="id575863"></a><a class="indexterm" name="id575872"></a><p><a name="function_match"></a> <a href="fulltext-search.html#function_match"><code class="literal">MATCH (<em class="replaceable"><code>col1</code></em>,<em class="replaceable"><code>col2</code></em>,...) AGAINST (<em class="replaceable"><code>expr</code></em> [<em class="replaceable"><code>search_modifier</code></em>])</code></a> </p> <pre class="programlisting"><em class="replaceable"><code>search_modifier:</code></em> { IN BOOLEAN MODE | WITH QUERY EXPANSION } </pre> <p> As of MySQL 3.23.23, MySQL has support for full-text indexing and searching: </p> <div class="itemizedlist"><ul> <li><p> A full-text index in MySQL is an index of type <code class="literal">FULLTEXT</code>. </p></li> <li><p> Full-text indexes can be used only with <code class="literal">MyISAM</code> tables, and can be created only for <a href="char.html" title="10.4.1. The CHAR and VARCHAR Types"><code class="literal">CHAR</code></a>, <a href="char.html" title="10.4.1. The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>, or <a href="blob.html" title="10.4.3. The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns. </p></li> <li><p> A <code class="literal">FULLTEXT</code> index definition can be given in the <a href="create-table.html" title="12.1.5. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement when a table is created, or added later using <a href="alter-table.html" title="12.1.2. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> or <a href="create-index.html" title="12.1.4. CREATE INDEX Syntax"><code class="literal">CREATE INDEX</code></a>. </p></li> <li><p> For large data sets, it is much faster to load your data into a table that has no <code class="literal">FULLTEXT</code> index and then create the index after that, than to load data into a table that has an existing <code class="literal">FULLTEXT</code> index. </p></li> </ul></div> <p> Full-text searching is performed using <a href="fulltext-search.html#function_match"><code class="literal">MATCH() ... AGAINST</code></a> syntax. <a href="fulltext-search.html#function_match"><code class="literal">MATCH()</code></a> takes a comma-separated list that names the columns to be searched. <code class="literal">AGAINST</code> takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name. There are three types of full-text searches: </p> <div class="itemizedlist"><ul> <li><p> A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “<span class="quote">some</span>” or “<span class="quote">then</span>” are stopwords and do not match if present in the search string. The <code class="literal">IN BOOLEAN MODE</code> modifier specifies a boolean search. For more information, see <a href="fulltext-boolean.html" title="11.9.2. Boolean Full-Text Searches">Section 11.9.2, “Boolean Full-Text Searches”</a>. </p></li> <li><p> A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given. </p></li> <li><p> A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The <code class="literal">WITH QUERY EXPANSION</code> modifier specifies a query expansion search. For more information, see <a href="fulltext-query-expansion.html" title="11.9.3. Full-Text Searches with Query Expansion">Section 11.9.3, “Full-Text Searches with Query Expansion”</a>. </p></li> </ul></div> <p> Constraints on full-text searching are listed in <a href="fulltext-restrictions.html" title="11.9.5. Full-Text Restrictions">Section 11.9.5, “Full-Text Restrictions”</a>. </p> <p> The <a href="myisam-ftdump.html" title="4.6.1. myisam_ftdump — Display Full-Text Index information"><span><strong class="command">myisam_ftdump</strong></span></a> utility can be used to dump the contents of a full-text index. This may be helpful for debugging full-text queries. See <a href="myisam-ftdump.html" title="4.6.1. myisam_ftdump — Display Full-Text Index information">Section 4.6.1, “<span><strong class="command">myisam_ftdump</strong></span> — Display Full-Text Index information”</a>. </p> </div> <div id="docnav"><a rel="prev" href="mysql-calendar.html" title="11.8 What Calendar Is Used By MySQL?">Previous</a> / <a rel="next" href="fulltext-natural-language.html" title="11.9.1 Natural Language Full-Text Searches">Next</a> / <a rel="up" href="functions.html" title="11 Functions and Operators">Up</a> / <a rel="contents" href="index.html">Table of Contents</a></div><br class="clear"/><div id="comments"><h1>User Comments</h1><table id="c1250" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Dyfed Lloyd Evans on October 21 2002 12:39am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=1250&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=1250">Edit</a>]</td></tr></table><p class="commenttext">Hyphen '-' characters break literals at the moment. <br/>A search for something like "GATA-D22S690" finds <br/>all entries containing GATA and not the full <br/>hyphenated text. The '-' character is treated as a <br/>word stop even within literals. The same is true if <br/>any of the special text search modifiers are used (eg <br/><pre>+, -, ~) so that hyphenated literals are not correctly <br/></pre>found with full text searches.<br/></p><table id="c1502" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Patrick O'Lone on December 9 2002 6:51am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=1502&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=1502">Edit</a>]</td></tr></table><p class="commenttext">It should be noted in the documentation that IN <br/>BOOLEAN MODE will almost always return a <br/>relevance of 1.0. In order to get a relevance that is <br/>meaningful, you'll need to:<br/><BR/><BR/><br/>SELECT MATCH('Content') AGAINST ('keyword1 <br/>keyword2') as Relevance FROM table WHERE MATCH<br/>('Content') AGAINST('+keyword1 +keyword2' IN <br/>BOOLEAN MODE) HAVING Relevance > 0.2 ORDER <br/>BY Relevance DESC<br/><BR/><BR/><br/>Notice that you are doing a regular relevance query <br/>to obtain relevance factors combined with a WHERE <br/>clause that uses BOOLEAN MODE. The BOOLEAN <br/>MODE gives you the subset that fulfills the <br/>requirements of the BOOLEAN search, the relevance <br/>query fulfills the relevance factor, and the HAVING <br/>clause (in this case) ensures that the document is <br/>relevant to the search (i.e. documents that score <br/>less than 0.2 are considered irrelevant). This also <br/>allows you to order by relevance.<br/><BR/><BR/><br/>This may or may not be a bug in the way that IN <br/>BOOLEAN MODE operates, although the comments <br/>I've read on the mailing list suggest that IN <br/>BOOLEAN MODE's relevance ranking is not very <br/>complicated, thus lending itself poorly for actually <br/>providing relevant documents. BTW - I didn't notice <br/>a performance loss for doing this, since it appears <br/>MySQL only performs the FULLTEXT search once, <br/>even though the two MATCH clauses are different. <br/>Use EXPLAIN to prove this.<br/></p><table id="c2263" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Nathan Ostgard on April 14 2003 8:25am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=2263&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=2263">Edit</a>]</td></tr></table><p class="commenttext">An easy solution to correct for spelling errors for small search items like the name of the city is to build a column that contains the SOUNDEX of each. I've found that using a 4 character SOUNDEX works the best. An example:<br/><br/>ALTER TABLE cities ADD city_soundex VARCHAR(4) NOT NULL;<br/>UPDATE cities SET city_soundex=LEFT(SOUNDEX(city_name),4);<br/><br/>And then to query against:<br/><br/>SELECT * FROM citites WHERE city_soundex=LEFT(SOUNDEX('Some City Name'),4);<br/></p><table id="c2855" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Jim Nguyen on June 18 2003 6:33pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=2855&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=2855">Edit</a>]</td></tr></table><p class="commenttext">The full-text search is slow when there are a lot of rows in the table. I have more than 2 million rows with text and multiple word searches (3 or more) take about 30 seconds to a minute or longer.<br/><br/>I am running a Athlon 2.2 Ghz with 512 MB DDR RAM 400 Mhz. Hard drive has seek time of 9 ms.<br/></p><table id="c3004" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Alan Riley on July 14 2003 11:46am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3004&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3004">Edit</a>]</td></tr></table><p class="commenttext">We too have a database with close to 6 million rows in it. We would like to use the fulltext search, but it is painfully slow. The sad thing is that any one query we need to run only needs to be run on a subset of the rows (think of those 6 million rows as being divided between about 80 different categories, with results only needed to be returned from within a category). It is a shame that there isn't some way for us to have a fulltext index within the rows of another column which is also indexed.<br/><br/>Our next shot at this is going to be to create 80 different tables, one for each category (yuck!), just to try to get decent performance on the within-category fulltext search.<br/><br/>I would think there is enough interest in fulltext search for there to be an email list dedicated to it where those of us who need to use it on real-world web sites could interact with the developers to try to tune it for the good of the community at large.<br/></p><table id="c3364" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Donal McMullan on September 24 2003 6:06am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3364&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3364">Edit</a>]</td></tr></table><p class="commenttext">The nature of fulltext indexing is such that the more 'words' (of greater than the minimum length) that appear in the columns you index, the greater will be size of the index, and the time it takes to create and search that index.<br/><br/>4 Million rows x 6 words per row = 24 Million entries<br/>4 Million rows x 30 words per row = 120 Million entries<br/><br/>If you index intelligently, you may well find that the feature meets your needs, despite what some users may have remarked above. "Number of rows" is useless as a benchmarking statistic on its own.<br/></p><table id="c3517" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Erlend Strømsvik on October 23 2003 8:41pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3517&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3517">Edit</a>]</td></tr></table><p class="commenttext">I've got a webshop running with mysql. We have 1.6 million booktitles in our database, and our fulltext index is from title and 'extra' title. Maybe around 5-7 words on average per record.<br/><br/>Any fulltext search in our database takes around 1-5ms. A three word search can take a few seconds but still faster than anything else...Even Amazon.<br/><br/>This is from our main server which has several thousand hits every day. We are one of the biggest booksellers in my country.<br/><br/>-This was in 'reply' to someone posting earlier about poor performance in 'real world situation'.<br/></p><table id="c3679" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Jerome C on December 3 2003 9:12pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3679&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3679">Edit</a>]</td></tr></table><p class="commenttext">BENCHMARK INFO!<br/><br/>We have a vast database of products (totaling 3 million rows).<br/><br/>The rebuild of the full-text index took 21 minutes to complete.<br/><br/>As for the search, a multiple word search on the full-text index of 1 field is as follows:<br/><br/>20 rows returned<br/>Query took 1.0263 sec<br/><br/>The size of the index is very big though.<br/>Index 449,529 KB<br/><br/>We are going to eliminate unnecessary words which should make the index smaller.<br/></p><table id="c3754" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Ben Margolin on December 19 2003 3:40am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3754&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3754">Edit</a>]</td></tr></table><p class="commenttext">The indexing process definitely depends on your machine config. I indexed the same dataset, same mysql version (4.0.16, -standard on Linux, -nt on XP).<br/><br/>First machine: a 512M Athlon 2100 running WinXP on a RAID1 array, and it took 9 mins 20 sec.<br/><br/>Second machine: 1G RAM P4-2.0ghz, running Linux 7.3 (2.4.20-13.7 kernel), one-spindle (but fast) IDE disk, and it took 29 mins 11 sec.<br/><br/>The query was "alter table msg_bodies add fulltext fti_bodies (subject, body);", and the dataset is about 550M, 1.3 million rows.<br/><br/>Both machines were mostly idle when the indexing occurred. I know it's just anecdotal, but if you run a big alter as I did, don't be surprised if it takes a while... oh yeah, and then be prepared for MUCH longer than normal insert times when you add large data to the FT indexed columns. This makes sense of course, but, at least on my dataset and hardware, is positively NOT neglible. FYI.<br/><br/></p><table id="c3941" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Jeffrey Yuen on February 2 2004 9:49pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3941&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3941">Edit</a>]</td></tr></table><p class="commenttext">Full text searching in 4.1.1<br/>For Chinese charset in UTF-8 encoding.<br/>It needed to set ft_min_word_len =1<br/><br/></p><table id="c3950" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Eric Jacolin on February 4 2004 5:39am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3950&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3950">Edit</a>]</td></tr></table><p class="commenttext">(MySQL 4.1)<br/>To make FULLTEXT MATCH work with Japanese UTF-8 text, be careful that words from your Japanese text be separated by the __ASCII__ space character, not Japanese UTF-8 (or other) spacing characters.<br/>(when using phpMyAdmin to manage data/ write a SQL query, you must switch away from your Japanese IME to insert a space char...)<br/></p><table id="c3953" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Tom Cunningham on February 5 2004 5:42am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3953&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=3953">Edit</a>]</td></tr></table><p class="commenttext">Tom's fulltext tips: To get MySQL searching well for me I did:<br/><br/>1. Have a normalized versions of the important columns: where you've stripped punctuation and converted numerals to words ('1' to 'one'). Likewise normalise the search string.<br/><br/>2. Have a combined fulltext index on your searchable columns to use in your 'WHERE' clause, but then have separate fulltext indexes on each column to use in the 'ORDER BY' clause, so they can have different weights.<br/><br/>3. For the scoring algorithm, include the independent importance of that record, and include a match of the inclusive index against stemmed versions of the search words (as: "wedding" => "wed", "weddings").<br/><br/>4. If there's exactly one result, go straight to that record.<br/><br/>5. If you get no results, try matching against the start of the most important column (WHERE column LIKE 'term%'), and put a 5-character index on that column. This helps if someone is searching on a very short word or a stopword.<br/><br/>6. Reduce minimum word length to 3, and make a new stopwords list just using "a an and the is in which we you to on this by of with". Use "REPAIR TABLE xxx QUICK" to rebuild the index and make a note of the index-file (xxx.MYI) size before and after you make changes. Then use ft_dump to tune.<br/></p><table id="c4256" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by James Munro on April 5 2004 12:11am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4256&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4256">Edit</a>]</td></tr></table><p class="commenttext">There's an interesting and helpful set of slides about fulltext search by Sergei Golubchik (the developer of this stuff) at <a href="https://web.archive.org/web/20130111042031/http://www.phpconference.de/2003/slides/database_track/golubchik_mysql_fulltext_search_2003.pdf">http://www.phpconference.de/2003/slides/database_track/golubchik_mysql_fulltext_search_2003.pdf</a><br/>It throws some light on quite a few of the questions posed here.<br/>Cheers<br/>James<br/></p><table id="c4332" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Attila Nagy on April 19 2004 11:29pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4332&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4332">Edit</a>]</td></tr></table><p class="commenttext">More about performance:<br/>Fulltext search in MySQL isn't slow really. It's slower than normal index selects, but that's not noticable. <br/>I'm playing tables that each contains ca 4million records, about 6GB of text that needs to be indexed. The problem lies in the optimization of the queries, that use the MATCH() and AGAINST() functions. So far as I found out MySQL can use in a query only one index. The optimizer looks for the index that will possibly give the smallest amount of rows, then goes trough all of these, and removes those records, that fall out because of the other WHERE statements.<br/>When you enter a fulltext search, the server _has_ to use that index. Any other statments could be applied only to the rows that were returned by the search. If you have lots of records, most of the searches will return lots of results. The rest of your query will be executed like if you were executing it against a table that contains the results, and no indexes - obviously that will be processed sequentially.<br/>Same thing applies when you use the SQL_CALC_FOUND_ROWS, or LIMIT with high offset values: first the server has to load all the results, then the limit could be applied.<br/>If you _have_ to use the fulltext, these are some hints how you could get quite good results:<br/> - Try to avoid any group, where, order and any statements for what it's necessary to get all the results. I know, usually this is impossible.<br/> - If you need to show the results on a web page, try to find other methods to get the total number of results than using SQL_CALC_FOUND_ROWS. Better if you don't tell the user at all how many results there are ;)<br/> - If indexing text that is in other language than english, before doing anything create a stopword file for your language! (That could reduce index size about 30%)<br/>But most important: think a lot, before you decide to use fulltext search!<br/></p><table id="c4336" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Peter Dunham on April 22 2004 12:23am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4336&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4336">Edit</a>]</td></tr></table><p class="commenttext">We are doing fulltext searches of around 200 tables of 1/4 -1/2 million rows each. Upgrading a twin cpu 2Ghz linux machine (running ES 3) from 1GB to 3GB RAM and increasing key_buffer_size from 384MB to 512MB has seen load averages go from 1.5-2 to around 0.5, with same usage.<br/><br/></p><table id="c4499" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Peter Grigor on May 29 2004 12:59pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4499&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4499">Edit</a>]</td></tr></table><p class="commenttext">Hi Attilla:<br/><br/>I'm not sure I agree with your comment that Mysql *always* uses a fulltext index if you include match...against.<br/><br/>I tried a sample query where I used match...against and another value in the where clause (on the primary keyed field) and Mysql's optimizer picked the primary key index.<br/><br/>The statement looked like "select something from sometable where match(col1, col2) against ('some text here') and pk_col = 44<br/></p><table id="c4639" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by JT Johnston on July 1 2004 2:45am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4639&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4639">Edit</a>]</td></tr></table><p class="commenttext"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html">http://dev.mysql.com/doc/mysql/en/Fulltext_Boolean.html</a> states you cannot do a full-text search in Boolean Mode by relevancy. You can, but you need mysql version 4+. Take note of "ORDER BY relevancy DESC".<br/><br/>Here is my code/example:<br/><br/>SELECT *,MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,JR,KW,AUS,GEO,AN,RB,CO,RR) AGAINST ('Margaret Atwood' IN BOOLEAN MODE) AS relevancy FROM<br/>ccl_main WHERE MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,JR,KW,AUS,GEO,AN,RB,CO,RR) AGAINST ('Margaret Atwood' IN BOOLEAN MODE) ORDER BY<br/>relevancy DESC;]<br/></p><table id="c4846" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by James Day on August 19 2004 12:18am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4846&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4846">Edit</a>]</td></tr></table><p class="commenttext">See The Full-Text Stuff That We Didn't Put In The Manual at <a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/tech-resources/articles/full-text-revealed.html">http://dev.mysql.com/tech-resources/articles/full-text-revealed.html</a> for more information about full-text search. <br/></p><table id="c4896" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by James on August 31 2004 4:08am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4896&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4896">Edit</a>]</td></tr></table><p class="commenttext">Thought some benchmarks for a very large data set might be useful to people. I created a full-text index on a 100 gig database, where the index was on a column where the data totals 3 gig. The index added 2 gig to the database. The database has 2,741,000 records. The textual content is technical literature in US English.<br/><br/>Machine: The machine is an Athlon XP 3200+ w/ 1 gig RAM, one drive for the OS, and another 250GB EIDE drive for the MySQL data. OS is Redhat 9.<br/><br/>Index creation: 3 hours 5 min. Most of that time was probably copying the table (since MySQL copies the table before it modifies it). The process was definitely disk-limited -- CPU usage was never regularly above 50%. I believe that were it not for the excess data to copy, the index creation would have taken about 45 minutes.<br/><br/>Queries were chosen to compare rare words, common words, and words of different lengths. All queries seemed to be disk-limited (CPU utilization hardly moved off baseline), which makes sense given that there is not enough RAM to hold the index in memory. All queries were done mutiple times and the average time reported. Results follow.<br/><br/>Word, Rows, Seconds, Seconds/Row<br/>----------------------------------------<br/>bilobed, 4, 0.15, 0.0375<br/>mends, 4, 0.19, 0.0475<br/>nanotechnology, 23, 0.64, 0.0278<br/>bioluminescent, 53, 1.53, 0.0289<br/>photosynthesis, 81, 2.21, 0.0273<br/>graphite, 5070, 123.00, 0.0243<br/>bicycle, 5385, 122.00, 0.0227<br/>titanium, 13503, 350.00, 0.0259<br/>(titanium,graphite), 18423, 425.00, 0.0231<br/>metal, 151095, 4020.00, 0.0266<br/><br/>This is just a small test on the way to indexing the full 100 gig in the database that I am working on for <a href="https://web.archive.org/web/20130111042031/http://www.freepatentsonline.com/">http://www.freepatentsonline.com</a>. I'll post results for that once I have a new server built.<br/></p><table id="c4917" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by noel darlow on September 3 2004 11:51pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4917&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4917">Edit</a>]</td></tr></table><p class="commenttext">In reply to Patrick O'Lone's post, above:<br/><br/>The first, non-boolean MATCH can't keep pace with the second, boolean one since it does not recognise the boolean operators. A search for foo* might turn up rows with foo, foobar, foofighters, etc but the non-boolean, relevance MATCH can't "count" anything except foo. Same problem with a phrase search.<br/><br/>Since effectively you can't use boolean operators in the second, boolean MATCH, it's rendered pointless.<br/><br/>Results could be post-processed with your own ranking algorithm but it's kind of odd that you can't do a boolean search AND rank results in the query. <br/><br/></p><table id="c4960" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Dave M on September 12 2004 3:44pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4960&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=4960">Edit</a>]</td></tr></table><p class="commenttext">Those looking to simply match a search phrase in a large group of words, try this:<br/><br/>SELECT * FROM data WHERE<br/>haystack LIKE ('%needle%')<br/>AND haystack RLIKE '[[:<:]]needle[[:>:]]'<br/><br/>This query will produce the same result as the following query, but is roughly 20X faster.<br/><br/>SELECT * FROM data WHERE<br/>haystack RLIKE '[[:<:]]needle[[:>:]]'<br/><br/>For more than one word use:<br/><br/>haystack LIKE ('%word1%word2%')<br/>AND haystack RLIKE '[[:<:]]word1[[:>:]].*[[:<:]]word2[[:>:]]'<br/></p><table id="c5028" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by James Day on September 26 2004 11:23am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5028&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5028">Edit</a>]</td></tr></table><p class="commenttext">The Wikipedia encyclopedia uses MySQL boolean full text search and MySQL 4.0.20. You can assess the speed yourselves. About 350,000 articles/ rows in the English language version, roughtly 5GB total. For all languages, one million articles and 10GB of text.<br/><br/>It's very important to have a sufficiently large key_buffer_size. Get much of the index in RAM and searches are typically very fast. Because we use InnoDB tables and can't share cache between InnoDB and MyISAM, we're moving to a setup which will use a dedicated and MyISAM tuned search server.<br/><br/>We run a slow query killer which will kill queries once their allowed time expires. That time depends on server load (active thread count). Documented at <a href="https://web.archive.org/web/20130111042031/http://wp.wikidev.net/Querybane">http://wp.wikidev.net/Querybane</a><br/><br/>It turns out that our current search often spends most time on things other than full text search - an improved version which is much more efficient is pending. Remember that you can use a self join to get around the one index per query limit of MySQL.<br/><br/>Search remains our most problematic load feature, requiring a couple of quite capable slaves to keep up at busy time. If we're working on the servers and the remainder can't handle the load, we switch to Google or Yahoo search. This is from one of the top few hundred sites on the net, so it's scaling pretty well for our application. One server was sufficiently fast to take us to the top 1,000.<br/></p><table id="c5072" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Andrew Panin on October 7 2004 1:05pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5072&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5072">Edit</a>]</td></tr></table><p class="commenttext">Hi all! I had a problem with FULLTEXT search and after I solved it, I want to try to help you. I thought that FULLTEXT search is slow. That was. But I did a simple trick:<br/><br/>1. For example, we have 4 tables with fulltext index. We need to perform fast search on them.<br/><br/>2. Do the following:<br/> CREATE TEMPORARY TABLE xxx SELECT id, name, MATCH(name) AGAINST ('search_string') AS relevancy FROM table1;<br/>INSERT INTO xxx SELECT id, name, MATCH(name) AGAINST ('search_string') AS relevancy FROM table2 ... <br/><br/>3. Then, when the temporary table is filled with the data, do the simple select from it:<br/><br/> SELECT id, name FROM xxx ORDER BY relevancy DESC<br/><br/>4. That's all.<br/><br/>I think, it is the optimal way to make a VERY FAST fulltext search from a number of tables. I hope, this will be helpful.<br/><br/></p><table id="c5458" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Dennis van den Ende on December 27 2004 1:43pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5458&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5458">Edit</a>]</td></tr></table><p class="commenttext">Allthough my fulltext serach works fine, i still have problems with it.<br/><br/>i compare products with there prices. i used the "relevance" idea (written above). But it will not recognize it correctly.<br/>Heres my query:<br/>SELECT *, MATCH( field ) AGAINST ('blabla') as relevance FROM `table` WHERE MATCH( field ) AGAINST ('blabla' IN BOOLEAN MODE ) HAVING relevance > 0.2<br/><br/>for example it finds 18 rows. to increase the rows, i checked (manuelly) the relevance. 3 above 18 (all 18.xxx) and the rest about 10.3 or lower. If i increase the "having" to 15, it finds only 2 of 3. <br/>The field i use is the only index-field and ofcourse a fulltext specific field.<br/>it seems that the relevance is taking part of the search results.<br/><br/>i am still looking for another idea but the relevance would cover my needs 100%<br/><br/>*update*<br/>I just updated the mysql version from 4.0.15 to 4.1.8 and it works perfectly...<br/></p><table id="c5679" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Mauricio Wolff on February 4 2005 1:35pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5679&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=5679">Edit</a>]</td></tr></table><p class="commenttext">to set up min_len and stopword_file in windows (win2k or xp):<br/><br/>1. run services.msc to check what .ini your mysql is reading (in my case, "C:\MySQL\bin\mysqld-nt" --defaults-file="C:\MySQL\my.ini" MySQL)<br/><br/>2. change your my.ini like this:<br/>[mysqld]<br/>ft_min_word_len=3<br/>ft_stopword_file="C:\\MySQL\\stop.txt"<br/><br/>3. restart your mysqld at services.msc<br/><br/>4. reindex your table using REPAIR TABLE tbl_name QUICK;<br/><br/>done!<br/></p><table id="c6030" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Peter Laursen on April 28 2005 9:39am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6030&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6030">Edit</a>]</td></tr></table><p class="commenttext">it is legal to use two different arguments with the "double match construction", i.e.<br/><br/>select * , match (artist,album,title) against ('blues in orbit') from musicfiles where match (artist,album,title) against ('ellington');<br/><br/>will FIND all records with 'ellington' as substring of artist, album or title, but will RATE them as the search match'es 'blues in orbit'<br/><br/>You can even .... ORDER BY or GROUP BY MATCH (kunstner,albumtitel,titel) AGAINST ('prelude to a kiss'); ... or against anything else!<br/></p><table id="c6183" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Grant Harrison on June 9 2005 8:48pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6183&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6183">Edit</a>]</td></tr></table><p class="commenttext">Maybe a little off topic here.<br/>Alternatively, instead of doing all full-text search within MySql<br/>database, you can pull data out and create an index on it.<br/>It's a much faster search.<br/><br/>I am using DBSight to search on 1.7million records of 1.2G data, on a P3 450MHz, 256MRam, with sub-second performance.<br/><br/>Taking search out of database also give you capability to customize stemmer and query parser.<br/></p><table id="c6296" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Erik Petersen on July 13 2005 8:06am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6296&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6296">Edit</a>]</td></tr></table><p class="commenttext">When using FULLTEXT queries on large set data sets it's critical to LIMIT your results. Doing some experimentation has made this very clear. Using a data set of 5.5 million rows of forum message posts indexed with a single FULLTEXT index:<br/><br/>select id, match(post) against('foo') as score from messages where match (body) against( 'foo' );<br/>...<br/>155323 rows in set (16 min 7.51 sec)<br/><br/>select id, match(post) against('foo') as score from messages where match (body) against( 'foo' ) limit 100;<br/>...<br/>100 rows in set (1.25 sec)<br/><br/>I ran a number of other tests on various terms known to be in the text with similar results.<br/><br/>These were run in reverse order shown. Make sure you return only the rows you need or you will suffer. For a search engine application returning pages of results keep in mind that nobody is going to ever see page 74! Cap the results to a reasonable maximum trading response time for completeness where possible.<br/></p><table id="c6480" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by kim markegard on August 25 2005 5:23pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6480&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6480">Edit</a>]</td></tr></table><p class="commenttext">In regards to Dyfed Lloyd Evans comment, I believe that "." will also cause this which is unfortunate because we have product name acronyms we'd like to seach.<br/></p><table id="c6774" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Nathan Huebner on October 29 2005 6:39pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6774&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6774">Edit</a>]</td></tr></table><p class="commenttext">After tons of hours today working on this, I HAVE FINALLY MASTERED THE TECHNIQUE OF USING THIS THING!!!!<br/><br/># This query is what you send to your MySQL to return the results. I put the LIMIT so that you don't overflow.<br/><br/>$query_ret="SELECT ProductID, Title, Description, Price, RetailPrice, MATCH (Title) AGAINST ('$keyword') AS score FROM server.book HAVING score > 0 LIMIT $start, $maxret;";<br/><br/># This query will COUNT the number of rows it found. (I believe that's correct), I don't believe it counts occurrences, just rows. I saw that if you pull back without a LIMIT above, and count that way, it's 100000x slower. So do your count like this:<br/><br/>$query_count="SELECT count(MATCH(Title) AGAINST('$keyword')) AS score FROM server.book WHERE MATCH (Title) AGAINST ('$keyword') HAVING score > 0;";<br/><br/>Make sure you have your Primary Key setup, your Title and Description as SEPARATE FULLTEXT INDEXES. I spent a few hours boggling over this.<br/><br/>Nathan<br/><br/></p><table id="c6907" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Saqib Aziz on November 24 2005 10:46pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6907&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6907">Edit</a>]</td></tr></table><p class="commenttext">There is no way to perdict what maximum relevance rank could be. While working with full text searches one may want to show percentages as the criteria for indicating how close a particular record was to the search query. To achieve this, one way is to select the maximum relevance rank or score and then use it( as a denominator ) with every single record score to get percentage equivalent of score.<br/><br/> For the sake of example, consider we have 6 as maximum rank and 2,3,4.234 are scores for three different records. Now to get percentage we have to do simple maths i.e., we can divide each score by 6(max rank) and then mulitply the result with 100. <br/><br/>(2/6)*100 = ..%<br/>(3/6)*100 = ..%<br/>(4.234/6)*100 = ..%<br/><br/>Hope this helps someone. <br/><br/>Saqib Aziz<br/></p><table id="c6984" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Gary Bickford on December 13 2005 10:49pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6984&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=6984">Edit</a>]</td></tr></table><p class="commenttext">The link in James Munro's comment regarding Sergei Golubchik's PHP Conference slides (<a href="https://web.archive.org/web/20130111042031/http://www.phpconference.de/2003/slides/database_track/golubchik_mysql_fulltext_search_2003.pdf">http://www.phpconference.de/2003/slides/database_track/golubchik_mysql_fulltext_search_2003.pdf</a>)<br/>no longer seems to work. <br/>The following link works, today at least. :) <a href="https://web.archive.org/web/20130111042031/http://www.php-kongress.de/2003/slides/database_track/golubchik_mysql_fulltext_search_2003.pdf">http://www.php-kongress.de/2003/slides/database_track/golubchik_mysql_fulltext_search_2003.pdf</a>.<br/><br/></p><table id="c7088" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Nathan Huebner on January 12 2006 2:59pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7088&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7088">Edit</a>]</td></tr></table><p class="commenttext">Tips on how to maximize performance, and minimize return time.<br/><br/>Let me guess: you have a lot of data, and you went ahead and FULLTEXT indexed a column within that large pile of data. To put it in simple words: not cool.<br/><br/>How long does your data take to return from a FULLTEXT search... 5 seconds? More? If you're returning data under 3 seconds, then you're ok, but if you want it to return in 1.3 seconds, here's what I did.<br/><br/>1. Dont fulltext index a column within a huge table. Instead, take a Unique Identifier, and the text you're searching, and copy it to another table.<br/><br/>Use this to export your columns:<br/><br/>SELECT uniquecolumn, mytextsearchcolumn FROM mydatabase.mytable INTO OUTFILE "c:/path/outfile.txt";<br/><br/>That will export your data to a file in Tab Delimited format.<br/><br/>Now, create a new table somewhere, and call it fulltextengine or something, with only 2 columns (less is better), you could add one more if you need to.<br/><br/>Now import your data:<br/><br/>LOAD DATA INFILE "c:/path/outfile.txt" IGNORE INTO TABLE mydatabase.fulltextengine;<br/><br/>Now, create your FULLTEXT index on your search field.<br/><br/>Great. Now you have a separate table for searching. So if you want to try it out, go into MySQL, and try this, or PHPmyAdmin:<br/><br/>SELECT SQL_CALC_FOUND_ROWS uniquecolumn, searchcolumn, MATCH (searchcolumn) AGAINST <br/>('Keyword Goes Here') AS score FROM mydatabase.fulltextengine HAVING score > 0;<br/><br/>Hope this helps!<br/><br/>It may add extra maintenance, but I will give up my soul to increase search speeds by 6x.<br/></p><table id="c7786" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Joe Soap on July 11 2006 4:31pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7786&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7786">Edit</a>]</td></tr></table><p class="commenttext">Not sure if this is useful for people trying to reduce their search dataset. But what I'm doing is preprocessing the text before I add it to the database. So I add the full text to my FileData column, but I also preprocess the text and put this processed text into a Keywords column. Then I search only the keywords column and never the full text.<br/><br/>This technique obviously (you'll see why) doesn't work for phrase matching but it may speed up the search time by reducing the size of the dataset to search. Here's the algorithm I use.<br/><br/>1. extract the text<br/>2. count each word of 2 or more characters in the text and record the frequency that each occurs<br/>3. from this list of words 2 or more characters long, remove the k (k currently = 500) most common words in the english dictionary<br/>4. sort the list so that the most frequently occurring words appear first<br/>5. take the first n words as the final keywords, where n > 0 and n < the total number of remaining words<br/></p><table id="c7842" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Tasuku SUENAGA on July 30 2006 8:27am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7842&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7842">Edit</a>]</td></tr></table><p class="commenttext">If you have performance problems on fulltext search,<br/>Please try Senna, fulltext search engine that can be embedded in MySQL.<br/><a href="https://web.archive.org/web/20130111042031/http://qwik.jp/senna/">http://qwik.jp/senna/</a><br/><br/>Original MySQL fulltext search is slow for query SELECT COUNT(*) or SELECT * FROM xx LIMIT large_number,x.<br/>These queries are very fast with Senna's 2ind patch.<br/><br/></p><table id="c7913" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Gary Osborne on August 19 2006 2:46am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7913&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=7913">Edit</a>]</td></tr></table><p class="commenttext">I'm not convinced that stop-words are of great value. Sure, they might reduce the size of the index and speed-up queries for some kinds of databases. But there are two fundamental flaws with "mandatory" stop-words. Firstly, without knowing in advance the nature of the data, how can any programmer proclaim to know which words should be excluded because they have no value? Secondly, if a user includes any stop word in a search query, then by definition that word's value can not be zero. If the word's value was zero, then why would the user use it in a search query?<br/><br/>If you need to disable stop-words without re-compiling, consider appending a character to the end of each word in your text before inserting your text into the database. I used "q". I also right-padded all words shorter than 4 characters to a length of 4 characters by appending "q"s. And I appended a " " to the end of my text string before inserting into the database. <br/><br/>After retrieving my text from the database, I did a global replace on my text. I changed "qqq" to "", "qq" to "", and "q " to " " - in that order - to restore the text to it's original form. That was not the best solution but it worked. Luckily for me, my text was simply a space delimited list of words without and punctuation. Otherwise, my "q" encoding and decoding would have been more difficult.<br/></p><table id="c8027" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Sebastian Alberoni on September 27 2006 8:59pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8027&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8027">Edit</a>]</td></tr></table><p class="commenttext">Combining MATCH with MAX, GROUP BY and ORDER BY can be of a lot of help when retrieving results in the correct order regarding relevance.<br/>For example, using this I could solve a problem with one table called 'Professional', which had a many-to-many reference to another table 'Profession'. Although I was using DISTINCT I was getting duplicate results because of different relevance values that MATCH was giving to the different entrances in 'Professions'.<br/>Here I am copying the final query that worked OK (it's a bit simplified):<br/><br/>select distinct p.idProfessional, MAX(MATCH (pssion.name, pssion.description) AGAINST ('string to search')) as professionRanking FROM professional p, professional_profession pp, profession pssion WHERE pp.Professional_idProfessional = p.idProfessional AND pp.Profession_idProfession = pssion.idProfession and ( MATCH (pssion.name, pssion.description) AGAINST ('string to search') GROUP BY p.idProfessional ORDER BY professionRanking DESC<br/></p><table id="c8632" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by miguel vm on May 20 2007 6:44pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8632&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8632">Edit</a>]</td></tr></table><p class="commenttext">The truth behind fulltext search, is that MySql first split text into single words, then indexes isolated words pointing to records. These are logical steps that many of us previously had tried before MySql fulltext commands creation. I created a PHP program some years ago to perform exactly the same split-and-index task. <br/><br/>This is the reason MATCH command allows prefixed wildcards but not postfixed wilcards. Since single words are indexed, a postfix wildcard is impossible to manage in the usual way index does. You can't retrieve '*nited states' instantly from index because left characters are the most important part of index.<br/><br/>Even so, I hope MySql developers some day implement postfix wildcars, because for many of us, it is important to perform a truly 'full text' search. To say something, if I have a record with the word 'database' , I want retrieve this record when searching by 'tabas', an impossible task for actual fulltext search command.<br/><br/>It's easy to see that such a command can gain lot of performance, even when MySql developers be obliged to search byte to byte into the indexed words.<br/><br/>If you have a big table with text descriptions , to say 1 GB size, it is possible that quantity of different words into text will not pass from 500.000, maybe 1.000.000 words, averaging 8 bytes each, total 8 MB of data to be browsed, instead the 1 GB you should seek byte to byte to find what you want. <br/><br/>This is a 1 GB / 8 MB = 125 , or two orders of magnitude lower in terms of processing.<br/><br/></p><table id="c8784" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Sergejzr Zr on July 27 2007 8:23pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8784&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8784">Edit</a>]</td></tr></table><p class="commenttext">Unfortunately it is not possible to combine Fulltext field and normal (i.e integer) field into one index. Since only one index per query can be used, that seems be a problem<br/><br/>Table:<br/>id(integer primary key)|content(text fulltext indexed)|status(integer key)<br/><br/>Note that executing folowing query, mysql will use only one index. Either fulltext, or status (Depending on intern statistics).<br/>Q1:<br/>SELECT * FROM table WHERE MATCH(content) AGAINST('searchQuery') AND status = 1<br/><br/>However it is still possible to use both indexes in one query. You will need a new index on id,status pair and use join. Thus mysql will be able to use one index for each table.<br/><br/>Q2:<br/>SELECT t1.* from table t1 LEFT JOIN table t2 ON(t1.id=t2.id) WHERE MATCH(t1.content)AGAINST('searchQuery') AND status=1<br/><br/>Q2 will run significantly faster than Q1 at least in my case :)<br/>Note the overhead: You will need an id for each row and a key wich is spanned over needed fields strating with id.<br/></p><table id="c8815" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Phoebe Bright on August 14 2007 2:06pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8815&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8815">Edit</a>]</td></tr></table><p class="commenttext">Using this for the first time I picked an unfortunate test and took a while before I worked out why it wasn't working. In the hopes of saving other newbies some time:<br/><br/>This will work:<br/>SELECT * FROM myfile WHERE description LIKE '%sea%'<br/><br/>But this will return nothing:<br/>SELECT * FROM myfile WHERE MATCH (description) AGAINST ('sea') <br/><br/>BECAUSE THE DEFAULT MIN LENGTH IS 4!<br/><br/>need to set ft_min_word_len to 3 in the configuration file if you want it to work.<br/><br/></p><table id="c8936" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Mohamed Mahir on September 28 2007 2:15am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8936&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8936">Edit</a>]</td></tr></table><p class="commenttext">To get the first exact matching record of the Full text search i wrote like this..<br/><br/>SELECT MATCH (column) AGAINST ('keyword') relevancy FROM t1 WHERE MATCH (column) AGAINST ('keyword') ORDER BY relevancy DESC LIMIT 1 <br/></p><table id="c8981" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Neil Delargy on October 19 2007 8:37am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8981&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=8981">Edit</a>]</td></tr></table><p class="commenttext">One solution to find a word with a dashes or hyphens in is to use FULL TEXT SEARCH IN BOOLEAN MODE, and to enclose the word with the hyphen / dash in double quotes.<br/></p><table id="c9031" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Derek Foreman on October 31 2007 4:20pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=9031&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=9031">Edit</a>]</td></tr></table><p class="commenttext">I use Mediawiki that makes use of the FullText searching and was not getting results I knew were in the database. After reading this page I realized that mysql won't index words 3 characters or less by default. The solution is detailed clearly in this page; <br/><br/>Change the ft_min_word_len setting. You can find what the server is using by running:<br/> <br/>SHOW VARIABLES LIKE 'ft%';<br/><br/>Then you'll have to rebuild the indexes on the tables that have FULLTEXT indices, because the server I'm using had several databases I needed a quick way to identify which tables these were. <br/><br/>SELECT DISTINCT TABLE_SCHEMA,<br/> TABLE_NAME<br/>FROM COLUMNS<br/>WERE COLUMN_KEY = 'MUL'<br/><br/>I could then rebuild the tables. <br/></p><table id="c9100" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Jane Doe on November 28 2007 3:39pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=9100&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=9100">Edit</a>]</td></tr></table><p class="commenttext">You could also try Sphinx, <a href="https://web.archive.org/web/20130111042031/http://sphinxsearch.com/">http://sphinxsearch.com/</a><br/><br/>Very fast and flexible, and works nice with MySQL.<br/><br/>Eliminates many of the issues mentioned here in the comments, also ;)<br/></p><table id="c9927" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Andreas Mayer on January 18 2009 2:47am</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=9927&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=9927">Edit</a>]</td></tr></table><p class="commenttext">It is possible to extract certain words directly from the full-text index (for instance, for an AutoSuggest feature). See <a href="https://web.archive.org/web/20130111042031/http://blog.dev001.net/2009/01/implementing-an-autosuggest-feature-using-mysql-fulltext-indices/">http://blog.dev001.net/2009/01/implementing-an-autosuggest-feature-using-mysql-fulltext-indices/</a><br/></p><table id="c11550" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Jackson Rollins on October 23 2010 7:23pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=11550&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=11550">Edit</a>]</td></tr></table><p class="commenttext">MATCH/AGAINST didn't work how I intended. Here's how I finally solved what I thought MATCH/AGAINST should have been doing from the beginning:<br/><br/><a href="https://web.archive.org/web/20130111042031/http://drupal.jacksonkr.com/content/searching-mysql-database-exact-word">http://drupal.jacksonkr.com/content/searching-mysql-database-exact-word</a><br/></p><table id="c11966" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Carlos Dias on August 8 2011 3:18pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=11966&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=11966">Edit</a>]</td></tr></table><p class="commenttext">Basically this approach makes me think twice because of the next logical steps:<br/>1- If your working in one table with a lot of records...each time the records are updated or new lines inserted the index must be (obviously)recreated ... if it's myisam ... @writing operations the table is locked...<br/>2- I guess that the best approach towards this it's probably the logic of: when tables are huge ... not creating indexes for text search ... create cache@sql ...(cache@sql is one index!) ...<br/><br/>Somehow anticipating these problems ... like i write are not problems to ignore...<br/><br/>Why this is the best option... because if people use one file to log the last writing operations and compare it with the file that contains the results @cache(best approach ... cronjob!) ... it's only necessary to point to the file that contains the results@cache...<br/><br/>The logic of:If there are 500 000 000 of conjugations of words/phrases,etc what's the need of indexing everything if only 50 000 conjugations are used/seeked,etc ...<br/><br/>Regards,<br/>Carlos<br/><br/> <br/></p><table id="c12203" class="commentdetails" summary="" width="100%" border="0" cellpadding="0" cellspacing="0"><tr><td>Posted by Bradley Smith on February 21 2012 2:35pm</td><td align="right">[<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=12203&action=delete">Delete</a>] [<a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?id=12203">Edit</a>]</td></tr></table><p class="commenttext">Alan, instead of creating 80 different tables, one for each category, why not partition the table by the category so the records with that category would be grouped together within the partition and then your only searching within the specific category and more direct and faster route to the data you want to search?<br/></p><p><a href="/web/20130111042031/http://dev.mysql.com/doc/mysql/comment.php?sect=fulltext-search">Add your own comment.</a></p></div><div id="docnav"><a href="#">Top</a> / <a rel="prev" href="mysql-calendar.html" title="11.8 What Calendar Is Used By MySQL?">Previous</a> / <a rel="next" href="fulltext-natural-language.html" title="11.9.1 Natural Language Full-Text Searches">Next</a> / <a rel="up" href="functions.html" title="11 Functions and Operators">Up</a> / <a rel="contents" href="index.html">Table of Contents</a></div></div></div> </div>  </div> </div> <div id="footer"> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/">Developer Zone</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/">Documentation</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/articles/">Developer Articles</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/news-and-events/">News & Events</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://forums.mysql.com/">Forums</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://lists.mysql.com/">Lists</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://bugs.mysql.com/">Bugs</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/worklog/">Worklog</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://planet.mysql.com/">Planet MySQL</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://labs.mysql.com/">Labs</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/">Downloads</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/mysql/">MySQL Community Server</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/mysql-proxy/">MySQL Proxy</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/cluster/">MySQL Cluster</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/workbench/">MySQL Workbench</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/downloads/connector/">MySQL Connectors</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://downloads.mysql.com/archives.php">Archives</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/">Documentation</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/">MySQL Reference Manuals</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-gui.html">MySQL Workbench</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-expert.html">Expert Guides</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-topic.html">Topic Guides</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-cluster.html">MySQL Cluster</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-other.html">Other Documents</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-about.html">About</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://dev.mysql.com/doc/index-archive.html">Archives</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/about/">About MySQL</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/about/contact/">Contact Us</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/buy-mysql/">How to Buy</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/partners/">Partners</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/about/jobs/">Job Opportunities</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/sitemap.html">Site Map</a></li> </ul> </div> <div class="links"> <ul> <li class="top"><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/about/legal/">Legal</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.mysql.com/about/legal/">Legal Policies</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.oracle.com/us/legal/privacy/index.htm">Your Privacy Rights</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.oracle.com/us/legal/terms/index.html">Terms of Use</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.oracle.com/us/legal/third-party-trademarks/index.html">Trademark Policy</a></li> <li><a href="https://web.archive.org/web/20130111042031/http://www.oracle.com/technetwork/community/oca-486395.html">Contributor Agreement</a></li> </ul> </div> <div id="search" class="en"> <form id="footer_search" action="https://web.archive.org/web/20130111042031/http://search.oracle.com/search/search" method="get"> <input type="text" id="f_q" name="q" value="" class="swap_value" onfocus="clearSearchText();"/> <input type="hidden" name="group" value="Documentation"/> <input type="image" src="/web/20130111042031im_/http://dev.mysql.com/common/themes/sakila/footer_search_g.png" id="f_go" alt="Search" title="Search"/> </form> </div> </div> <div id="copyright-oracle"><a href="https://web.archive.org/web/20130111042031/http://www.oracle.com/"><img src="/web/20130111042031im_/http://dev.mysql.com/common/logos/logo-oracle-red-91x22.gif" alt="Oracle" width="91" height="22"/></a>  <span>© 2013, Oracle Corporation and/or its affiliates</span></div> <script src="/web/20130111042031js_/http://dev.mysql.com/common/js/metrics/s_code_remote.js"></script> </body> </html>