MySQL match() against() – order by relevance and column?

by

This might give the increased relevance to the head part that you want. It won’t double it, but it might possibly good enough for your sake:

SELECT pages.*,
       MATCH (head, body) AGAINST ('some words') AS relevance,
       MATCH (head) AGAINST ('some words') AS title_relevance
FROM pages
WHERE MATCH (head, body) AGAINST ('some words')
ORDER BY title_relevance DESC, relevance DESC

-- alternatively:
ORDER BY title_relevance + relevance DESC

An alternative that you also want to investigate, if you’ve the flexibility to switch DB engine, is Postgres. It allows to set the weight of operators and to play around with the ranking.

More Related Contents:

  1. Comparison of full text search engine – Lucene, Sphinx, Postgresql, MySQL? [closed]
  2. Search for “whole word match” in MySQL
  3. Choosing a stand-alone full-text search server: Sphinx or SOLR? [closed]
  4. mysql fulltext search failure
  5. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  6. Django MySQL full text search
  7. MySQL Fulltext Search Score Explained
  8. How to allow fulltext searching with hyphens in the search query
  9. MySQL Error 1093 – Can’t specify target table for update in FROM clause
  10. MySQL update table based on another tables value
  11. On Duplicate Key Update same as insert
  12. MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes
  13. conditional join in mysql
  14. Select the 3 most recent records where the values of one column are distinct
  15. How to remove leading and trailing whitespace in a MySQL field?
  16. How to reset the root password in MySQL 8.0.11?
  17. What characters have to be escaped to prevent (My)SQL injections?
  18. Is there any performance gain in indexing a boolean field?
  19. NodeJS responded MySQL timezone is different when I fetch directly from MySQL
  20. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ [duplicate]
  21. How to swap values of two rows in MySQL without violating unique constraint?
  22. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  23. Storing images in MySQL [closed]
  24. Would it help to add index to BIGINT column in MySQL?
  25. MySql Query Replace NULL with Empty String in Select
  26. how to trim leading zeros from alphanumeric text in mysql function
  27. What are the max number of allowable parameters per database provider type?
  28. How to handle a query with special characters / (forward slash) and \ (backslash)
  29. Does MySQL support historical date (like 1200)?
  30. mySQL query to search all tables within a database for a string?

Leave a Comment