How To: MySQL FullText searching
How To: MySQL FullText searching
Perhaps in the future DataTables might directly support FullText fields?
...Some background ...
FullText indexes and searching are supported in MySQL 3.23.23 and above and allow programmers and users to implement keyword based filtering and sorting, and provides several modes like Natural Language mode (default) and Boolean mode. I'll talk a little about these modes below. FullText statements in your SQL can go into your SELECT, or WHERE, but use an aliased value in the ORDER BY portions. Here is an example of a FullText query (note you must have defined your FullText indexes in the database before calling against the index, in the example the index is defined on headline and story):
SELECT headline, story, MATCH(headline,story) AGAINST ('Hurricane damage recent') as score
WHERE MATCH (headline,story) AGAINST ('Hurricane damage recent')
ORDER BY score DESC;
In the default Natural Language mode, MATCH .. AGAINST returns a floating point numeric rank (higher is more relevant). This allows you to sort results if you wish, and I've aliased the result to score and used it in my ORDER BY clause. Note that although I've used MATCH .. AGAINST twice in the query, MySQL is smart enough to have cached the first call so there is no performance penalty for the second call.
Some basic things to know about FullText is that white space and punctuation are ignored, and each word (separated by white space) is tokenized and used as a separate term - this makes this search different than DataTables' filters. There is both an upper and lower length limit for valid terms (default is 4 on the low side, but these are configurable) and words matching the Stop Words list are ignored (common words like "the", "and"; there's a default list but this can be configured as well).
... Performance ...
Of course features don't come without some cost, but FullText runs smoothly on datasets up to several million rows large. The performance cost is primarily in the UPDATE phase, in which the index must also be updated. For datasets larger than several million, you might want to look into alternative programs like Sphinx (http://sphinxsearch.com/) or Apache's Solr/Lucene (http://lucene.apache.org/solr/) or others.
... Natural Language mode vs Boolean mode ...
As mentioned above, Natural Language mode generates a relevance score for indexes matching your search terms so you can rank rows that have more than one hit against the index higher in the results list. This mode, however, doesn't have more powerful features like specifying that a term must be included, or should not be included. Boolean mode allows some of these features, but does not provide a relevance score (I may be wrong about this, check the documentation), so there's a trade-off. Operators in Boolean mode include the '+' (boolean AND; must include), '-' (boolean NOT; must not include) and '( )' for grouping terms, i.e. "+Reagan -Bush (Iran Contra)". There are other operators that let you indicate an increased or decreased weighting, they are like + and - but not as strict; these operators are '>', '<', '~', etc. See more at http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html.