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:
CREATE TABLE `book` (
Before using the full text search, index the columns
ALTER TABLE book ADD FULLTEXT(title, keywords, description);
Let’s verify that it already in Full Text (e.g. shows
book table’s column)
SELECT index_name, group_concat(column_name) as columns
Objective: search result must shows the result that match the
title first, then book’s
description, followed by category’s
Let see how the sql look like
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)