Full Text search will be useful when we require search on Large Text and where relevancy of search term is important.
To begin with, let's look at why LIKE may not be the most effective search. When you use LIKE, especially when you are searching with a % at the beginning of your comparison, MySQL Server needs to perform both a table scan of every single row and a byte byte by byte check of the column you are checking.
Example: We have a whole bunch fo queries that "search" for clients, customers, etc. You can search by firstname, email, etc. We're using LIKE statements in the following manner:
select * from customer where fname like '%someName%'
Where as Full text search is in the following manner:
MATCH (table_field_name) AGAINST ("+some name" IN BOOLEAN MODE)
The pattern in AGAINST() must be a constant string, not a query parameter and we can also generate score on the basis of matching word. (For example, currently we are using fulltext search on our search engine http://offerson.com and you can check the same).
We can set Full Text in MySQL my making the index as a fulltext and we can also set the minimum fulltext length by changing "ft_min_word_len" in my.cnf file. After changing the value of "ft_min_word_len" we need to restart the mysql. By default "ft_min_word_len" is set to 4.
ft_min_word_len configuration variable using PHP?
How can I get the value of MySQL's 'ft_min_word_len';
configuration variable using PHP?
Show variables like 'ft_min%'
eg. select variable_value from information_schema.global_variables where variable_name like 'ft_min%';
MySQL has support for full-text indexing and searching:
A full-text index in MySQL is an index of type
Full-text indexes can be used only with
MyISAM tables, and can be created only for
FULLTEXT index definition can be given in the
CREATE TABLE statement when a table is created, or added later using
ALTER TABLE or
For large data sets, it is much faster to load your data into a table that has no
FULLTEXT index and then create the index after that, than to load data into a table that has an existing
Full-text searching is performed using
MATCH() ... AGAINST syntax.
MATCH() takes a comma-separated list that names the columns to be searched.
AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.
There are three types of full-text searches:
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 “some”or “then” are stopwords and do not match if present in the search string. The
IN BOOLEAN MODE modifier specifies a boolean search. For more information, see Section 12.9.2, “Boolean Full-Text Searches”.
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.
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
WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 12.9.3, “Full-Text Searches with Query Expansion”.