MYSQL fulltext search order by relevance

Question

I am trying to get my Full text search to order by relevance. here is my code it works if remove the ORDER BY but doesn't sort by relevance. I have tried this and it actually makes it so it doesnt find any results at all... Any ideas? ``` $query_for_result=mysql_query(" SELECT * FROM Assets WHERE MATCH (`Badge`,`First Name`,`Last Name`,`Service Tag`,`Asset Tag`) AGAINST ('".$query."*' IN BOOLEAN MODE) and `deleted` = '0' ORDER BY relevance DESC"); ``` edit* ```

Search by: Badge, First or Last Name, Service Tag, Asset Tag, Printer Queue or Printer IP.

Accepted Answer

From MySQL Boolean Full-Text Searches documentation:

They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains “MySQL” twice, but it is listed last, not first.

That explains why it is not sorted by relevance without the `ORDER BY`. Now to be able to order by `relevance`, you need to define it:

```
SELECT *, MATCH (`Badge`,`First Name`,`Last Name`,`Service Tag`,`Asset Tag`) as relevance
WHERE MATCH AGAINST ('".$query."' IN BOOLEAN MODE) and `deleted` = '0'
ORDER BY relevance DESC
```

MYSQL fulltext search order by relevance

Solution

Problem