MySQL search order by relevance column using FULLTEXT

I’ve blamed by one of the client and says that the keyword that match the title shown at bottom. So want me to order by column match.

Finally I found the solution which is Full-Text Search.

NOTE: In MySQL 5.5, full text search only applicable on MyISAM, only MySQL 5.6 onward can used in InnoDB

Let say I have 2 tables: book & category

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

CREATE TABLE `book` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(100) NOT NULL, `description` varchar(1000) NOT NULL, `keywords` varchar(200) NOT NULL, `category_id` int(8) unsigned, PRIMARY KEY (`id`) ) ENGINE=MyISAM; CREATE TABLE `category` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `description` varchar(1000) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;

Before using the full text search, index the columns

1 2	ALTER TABLE book ADD FULLTEXT(title, keywords, description); ALTER TABLE category ADD FULLTEXT(name, description);

Let’s verify that it already in Full Text (e.g. shows book table’s column)

1 2 3 4 5 6

SELECT index_name, group_concat(column_name) as columns FROM information_Schema.STATISTICS WHERE table_schema = 'my_db_name' AND table_name = 'book' AND index_type = 'FULLTEXT' GROUP BY index_name

Objective: search result must shows the result that match the title first, then book’s keywords& description, followed by category’s name & description

Let see how the sql look like

1 2 3 4 5 6 7 8

SELECT book.* , MATCH (book.title) AGAINST ('PHP MySQL' IN BOOLEAN MODE) AS relevance_1 , MATCH (book.keywords, book.description) AGAINST ('PHP MySQL' IN BOOLEAN MODE) AS relevance_2 , MATCH (category.name, category.description) AGAINST ('PHP MySQL' IN BOOLEAN MODE) AS relevance_3 FROM book LEFT JOIN category ON category.id = book.category_id WHERE MATCH (book.title, book.keywords, book.description, category.name, category.description) AGAINST ('PHP MySQL' IN BOOLEAN MODE) ORDER BY (relevance_1 * 3) + (relevance_2 * 2) + relevance_3 DESC

The IN BOOLEAN MODE will return result either 1 or 0.

In the last row (ORDER BY), there is multiplication, that is weightage

• relevance_1 * 3 - the most important
• relevance_2 * 2 - the second important
• relevance_3 - the least important (no multiply anything)

So order them descendingly will give the result most important first (higher the number, the more important)