Home > MySQL > Relevant search in MySQL

Relevant search in MySQL

September 11th, 2009 Leave a comment Go to comments

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
Categories: MySQL Tags:
  1. Jerold Belshaw
    November 26th, 2010 at 11:34 | #1

    Love your site man keep up the good work

  2. Anonymous
    January 24th, 2011 at 10:48 | #2

    Im glad to see this informative article. Thanks for such helpful and useful post.

  1. No trackbacks yet.