Alex Blog

We have an query. Query is a long phrase, for example 4 words. We want to found in table most relevant results.

There is built-in operator in MySQL which called MATCH – AGAINST. This operator executes relevant search in specified FULLTEXT indexes.

Our table:

CREATE TABLE `clients` (
`id` int(10) unsigned NOT NULL auto_increment,
`firm` varchar(150) NOT NULL default '',
`name_` varchar(30) NOT NULL default '',
`address` varchar(150) default NULL,
PRIMARY KEY  (`id`),
FULLTEXT KEY `firm` (`firm`,`name_`,`address`)
) ;

To find all rows which fields `firm`,`name_`,`address` contain ‘lawyer Braun park avenue’ use the query (for MySQL 5.0)

SELECT * FROM `clients` WHERE MATCH ( `firm`,`name_`,`address` ) AGAINST ( 'lawyer Braun park avenue' )

Important. All fields you are searching through must be in one FULLTEXT index. You can create FULLTEXT index for each search type.

Important. If you want to search by a part of word you can be dissapointed. While MySQL indexes the words from the start, the Fulltext-search also available only with ending wildcard * (wildcards enabled only in boolean mode). When you using a boolean mode, you need to sort the results manually with ORDER clause.

SELECT *, MATCH ( `firm`,`name_`,`address` ) AGAINST ( 'lawy* Braun park avenu* IN BOOLEAN MODE' ) AS `score` FROM `clients` WHERE MATCH ( `firm`,`name_`,`address` ) AGAINST ( 'lawy* Braun park avenu* IN BOOLEAN MODE' ) ORDER BY `score` DESC