CODEWITHSUNDEEP
mysql
lyborko
lyborko

Reputation: 2619

Full-text search on my MySQL table according to "field weight/order"

I would like to use full-text search on my MySQL table, but in addition to it, I would like also imply some kind of "weight order" for columns, in which the search is performed.

For example : I have three columns named - ChapterCol, TextCol, NoteCol. When I search for the word "financial" it should return : at the first place all rows which contains this word in ChapterCol (and/or TextCol, NoteCol too), then all rows which has it in TextCol (and/or NoteCol) and at last all rows which has it only in NoteCol.

I hope there is an answer...

thanx a lot

Upvotes: 1

Views: 1149

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

First, you need to use the MyISAM storage engine sicne it's the only one in MySQL that supports FULLTEXT indexes.

Create 3 separate fulltext indexes, one on each of those columns.

In your SELECT query, do a MATCH...AGAINST... for each of the 3 columns in both the WHERE clause and the ORDER BY clause so you can get all rows that have your search term in at least one of the 3 columns, and sort them based on which column(s) include(s) the search term.

Here's an example:

CREATE TABLE IF NOT EXISTS your_table (
ChapterCol TEXT, 
TextCol TEXT, 
NoteCol TEXT,
FULLTEXT INDEX (ChapterCol),
FULLTEXT INDEX (TextCol),
FULLTEXT INDEX (NoteCol)
) Engine = MyISAM;

-- insert test values
insert into your_table (ChapterCol,TextCol,NoteCol) values ('foo','foo','foo'),
('financial blah blah','foo','foo'),('foo','financial blah blah','foo'),
('foo','foo','financial blah blah'),('financial blah blah','financial blah blah',
'financial blah blah');

-- insert filler
insert into your_table (ChapterCol,TextCol,NoteCol) 
values (md5(rand()),md5(rand()),md5(rand())),(md5(rand()),md5(rand()),md5(rand())),
(md5(rand()),md5(rand()),md5(rand())),(md5(rand()),md5(rand()),md5(rand())),
(md5(rand()),md5(rand()),md5(rand())),(md5(rand()),md5(rand()),md5(rand())),
(md5(rand()),md5(rand()),md5(rand())),(md5(rand()),md5(rand()),md5(rand())),
(md5(rand()),md5(rand()),md5(rand())),(md5(rand()),md5(rand()),md5(rand())),
(md5(rand()),md5(rand()),md5(rand())),(md5(rand()),md5(rand()),md5(rand()));

SELECT ChapterCol,TextCol,NoteCol
FROM your_table
WHERE MATCH (ChapterCol) AGAINST ('financial' IN BOOLEAN MODE)
OR MATCH (TextCol) AGAINST ('financial' IN BOOLEAN MODE)
OR MATCH (NoteCol) AGAINST ('financial' IN BOOLEAN MODE)
ORDER BY MATCH (ChapterCol) AGAINST ('financial' IN BOOLEAN MODE) DESC,
MATCH (TextCol) AGAINST ('financial' IN BOOLEAN MODE) DESC,
MATCH (NoteCol) AGAINST ('financial' IN BOOLEAN MODE) DESC;

Upvotes: 1

Related Questions