MATCH() AGAINST() Function SQL

The MATCH()...AGAINST() syntax is used to search in FULLTEXT indexes

An index is a way to perform searches more quickly through tables. In general, searches cannot be performed on large text files unless they have first been associated with a FULLTEXT index. The index is created in the CREATE TABLE statement or the ALTER TABLE statement.

ALTER TABLE table ADD FULLTEXT(column1, column2, etc.)

To search the FULLTEXT index, the combination of MATCH()...AGAINST() is used. The MATCH portion defines the particular columns to be searched through their indexes, and the AGAINST() portion contains the keyword or keywords to be searched. Syntax:

SELECT column(s) FROM table WHERE MATCH(column) AGAINST('string')

Results of this natural language query are listed in order of relevance. AGAINST() may contain a second parameter that defines the type of search to be made. IN BOOLEAN MODE allows specific items to be excluded from a search, for example:

SELECT column1, column2, etc. FROM table WHERE MATCH (column(s)) AGAINST ('string -exclusion' IN BOOLEAN MODE)

Meanwhile WITH QUERY EXPANSION allows for a deeper search.

In general, FULLTEXT queries are not case sensitive. Minimum word length is four characters. Other common words such as "this", "that", and "other" are ignored. Words found in more than half the rows are also ignored. In mySQL the myISAM storage engine may need to be installed.

< MAKE_SET Function SQL | MAX MIN functions >


Send notes in disappearing ink!

Interesting Pages